﻿Imports System.Data.Odbc
Imports System.Data
Imports System.Data.SqlClient
' NOTA: puede usar el comando "Cambiar nombre" del menú contextual para cambiar el nombre de clase "Service1" en el código, en svc y en el archivo de configuración.
Public Class Service1
    Inherits ConexionSQL
    Implements IService1

    Dim sConn As String = ""

    Public Sub New()
    End Sub

    Public Function GetData(ByVal value As Integer) As String Implements IService1.GetData
        Return String.Format("You entered: {0}", value)
    End Function

    Public Function GetDataUsingDataContract(ByVal composite As CompositeType) As CompositeType Implements IService1.GetDataUsingDataContract
        If composite Is Nothing Then
            Throw New ArgumentNullException("composite")
        End If
        If composite.BoolValue Then
            composite.StringValue &= "Suffix"
        End If
        Return composite
    End Function

    Public Function obtenerAgenciaTransporteDBF(agencia As String) As Agencia Implements IService1.obtenerAgenciaTransporteDBF
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\TABLAS\\INFOGEN.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sSelect As String = "SELECT item, ruc, descrip, domi2, longitud, latitud, item2 FROM INFOGEN WHERE code+subcode='1201' and ITEM='" & agencia & "'"
        Dim agen As New Agencia

        Using connection As New OdbcConnection(sConn)
            connection.Open()
            Dim command As New OdbcCommand(sSelect, connection)
            Dim reader As OdbcDataReader = command.ExecuteReader()
            While reader.Read()
                agen.AGE_Codigo = reader.GetString(0).Trim
                agen.AGE_RUC = reader.GetString(1).Trim
                agen.AGE_Descripcion = Replace(reader.GetString(2).Trim, "'", " ")
                agen.AGE_Direccion = reader.GetString(3).Trim
                agen.AGE_Longitud = reader.GetString(4).Trim
                agen.AGE_Latitud = reader.GetString(5).Trim
                agen.DIS_Codigo = reader.GetString(6).Trim
                agen.AGE_Activo = "1"
            End While

            ' Call Close when done reading.
            reader.Close()
        End Using

        Return agen
    End Function

    Public Function obtenerPeriodo() As String
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\CFG\\MATROCFG.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sSelect As String = "SELECT anhos, meses FROM matrocfg WHERE codcia='01'"
        Dim periodo As String = ""
        Using connection As New OdbcConnection(sConn)

            connection.Open()
            Dim command As New OdbcCommand(sSelect, connection)

            Dim reader As OdbcDataReader = command.ExecuteReader()
            While reader.Read()
                periodo = reader.GetString(0).Substring(2, 2) + "" + reader.GetString(1)
            End While

            reader.Close()
        End Using

        Return periodo
    End Function

    Function obtenerArticulo(codVendedor As String, empresa As String, certificado As String, articulo As String) As Articulo Implements IService1.obtenerArticulo
        Dim servics As New SeguridadEnvio
        Dim encrip As String = servics.generarCodigoEncriptado(codVendedor, empresa)
        Dim art As Articulo = Nothing

        If encrip.Equals(certificado) Then
            Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\" + obtenerPeriodo() + "\\INVMAST.DBF"
            sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
            Dim sSelect As String = "SELECT item, descrip, medida, clase, lista, tmlis, lista1, tmlis1, lista2, tmlis2,cosprom, promdol, prov1, ubica, ubicamas, largo, combo, mano-compr, cant2, percepcion, mconstruc, '1' FROM INVMAST WHERE item='" + articulo + "' and ITEM IS not null and DESCRIP IS not null"

            Using connection As New OdbcConnection(sConn)
                connection.Open()
                Dim command As New OdbcCommand(sSelect, connection)
                Dim reader As OdbcDataReader = command.ExecuteReader()
                'ARRAY LIST

                art = New Articulo
                While reader.Read()
                    art.ART_Codigo = reader.GetString(0).Trim
                    art.ART_Descripcion = reader.GetString(1).Trim
                    art.UND_Codigo = reader.GetString(2).Trim
                    art.CLA_Codigo = reader.GetString(3).Trim
                    art.ART_LPrecio1 = reader.GetDouble(4)
                    art.ART_MLPrecio1 = reader.GetString(5)
                    art.ART_LPrecio2 = reader.GetDouble(6)
                    art.ART_MLPrecio2 = reader.GetString(7)
                    art.ART_LPrecio3 = reader.GetDouble(8)
                    art.ART_MLPrecio3 = reader.GetString(9)
                    art.ART_Costos = reader.GetDouble(10)
                    art.ART_CostoD = reader.GetDouble(11)
                    art.PROV_Codigo = reader.GetString(12).Trim
                    art.ART_Ubicacion = reader.GetString(13).Trim
                    art.ART_UbicaMas = reader.GetString(14).Trim
                    art.ART_Largo = reader.GetString(15).Trim
                    art.ART_Combo = reader.GetString(16).Trim
                    art.ART_Stock = reader.GetDouble(17)
                    art.ART_UndDespacho = reader.GetDouble(18)
                    art.ART_Percepcion = reader.GetString(19).Trim
                    art.ART_MConstruccion = reader.GetString(20).Trim
                    art.ART_CantxLlegar = ""
                    art.ART_ProxLlegada = ""
                    art.ART_Activo = reader.GetString(21).Trim
                End While

                ' Call Close when done reading.
                reader.Close()

            End Using
        End If

        Return art
    End Function

    Private Function tcComercialVenta(ByVal anio As String, ByVal mes As String) As Double
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\CFG\\CAMBIOS.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sSelect As String = "SELECT ZTCVTA FROM CAMBIOS.DBF WHERE ANO = '" & anio & "' AND MES = '" & mes & "'"
        Dim cambio As Double = 1
        Using connection As New OdbcConnection(sConn)
            Dim command As New OdbcCommand(sSelect, connection)

            connection.Open()

            Dim reader As OdbcDataReader = command.ExecuteReader()
            While reader.Read()
                cambio = reader.GetDouble(0)
            End While

            reader.Close()
        End Using

        Return cambio
    End Function

    Private Function tcComercialCompra(ByVal anio As String, ByVal mes As String) As Double
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\CFG\\CAMBIOS.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sSelect As String = "SELECT ZTCCOM FROM CAMBIOS.DBF WHERE ANO = '" & anio & "' AND MES = '" & mes & "'"
        Dim cambio As Double = 1
        Using connection As New OdbcConnection(sConn)
            Dim command As New OdbcCommand(sSelect, connection)

            connection.Open()

            Dim reader As OdbcDataReader = command.ExecuteReader()
            While reader.Read()
                cambio = reader.GetDouble(0)
            End While

            reader.Close()
        End Using

        Return cambio
    End Function

    Private Function obtenerDescuentos(ByVal articuloId As String) As Descuentos()
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\TABLAS\\DESCUENT.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sSelect As String = "SELECT cantidad, descuento FROM descuent WHERE codarti='" + articuloId + "'"
        Using connection As New OdbcConnection(sConn)
            connection.Open()
            Dim command As New OdbcCommand(sSelect, connection)
            Dim reader As OdbcDataReader = command.ExecuteReader()

            Dim lista As New ArrayList
            While reader.Read()
                Dim descuento As New Descuentos
                descuento.DES_Cantidad = Math.Round(reader.GetDouble(0), 4)
                descuento.DES_Porcentaje = Math.Round(reader.GetDouble(1), 4)
                lista.Add(descuento)
            End While

            Dim i As Integer = 0

            Dim descuentos(lista.Count - 1) As Descuentos
            While lista.Count > i
                descuentos(i) = lista(i)
                i = i + 1
            End While

            ' Call Close when done reading.
            reader.Close()

            Return descuentos
        End Using

        Return Nothing
    End Function

    Function obtenerArticulosEsp(busqueda As String) As Articulo() Implements IService1.obtenerArticulosEsp
        busqueda = busqueda.ToUpper
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\" + obtenerPeriodo() + "\\INVMAST.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sSelect As String = "SELECT item, descrip, medida, clase, lista, tmlis, lista1, tmlis1, lista2, tmlis2,cosprom, " & _
        " promdol, prov1, ubica, ubicamas, largo, combo, mano-compr, cant2, percepcion, mconstruc, '1' " & _
        " FROM INVMAST " & _
        " WHERE SUBSTR(ITEM,1,1) != 'G' and SUBSTR(ITEM,1,1) != 'B' and SUBSTR(ITEM,1,1) != 'E' and LEN(ITEM)>6 and " & _
        " ITEM IS not null and DESCRIP IS not null and (UPPER(ITEM)  like '%" + busqueda + "%' or UPPER(DESCRIP) like '%" + busqueda + "%')"

        Using connection As New OdbcConnection(sConn)
            connection.Open()
            Dim command As New OdbcCommand(sSelect, connection)
            Dim reader As OdbcDataReader = command.ExecuteReader()

            Dim art As New Articulo
            Dim lista As New ArrayList
            Dim anio As String = Format(Year(Now), "0")
            Dim mes As String = Format(Month(Now), "00")
            Dim tcCVta As Double = tcComercialVenta(anio, mes)
            Dim tcCCompra As Double = tcComercialCompra(anio, mes)

            While reader.Read()
                art.ART_Codigo = reader.GetString(0).Trim
                art.ART_Descripcion = reader.GetString(1).Trim
                art.UND_Codigo = reader.GetString(2).Trim
                art.CLA_Codigo = reader.GetString(3).Trim
                art.ART_LPrecio1 = reader.GetDouble(4)
                art.ART_MLPrecio1 = reader.GetString(5)
                art.ART_LPrecio2 = reader.GetDouble(6)
                art.ART_MLPrecio2 = reader.GetString(7)
                art.ART_LPrecio3 = reader.GetDouble(8)
                art.ART_MLPrecio3 = reader.GetString(9)
                art.ART_Costos = reader.GetDouble(10)
                art.ART_CostoD = reader.GetDouble(11)
                art.PROV_Codigo = reader.GetString(12).Trim
                art.ART_Ubicacion = reader.GetString(13).Trim
                art.ART_UbicaMas = reader.GetString(14).Trim
                art.ART_Largo = reader.GetString(15).Trim
                art.ART_Combo = reader.GetString(16).Trim
                art.ART_Stock = reader.GetDouble(17)
                art.ART_UndDespacho = reader.GetDouble(18)
                art.ART_Percepcion = reader.GetString(19).Trim
                art.ART_MConstruccion = reader.GetString(20).Trim
                art.ART_CantxLlegar = ""
                art.ART_ProxLlegada = ""
                art.ART_Activo = reader.GetString(21).Trim
                art.ART_Descuentos = obtenerDescuentos(reader.GetString(0).Trim)
                art.TCCVenta = tcCVta
                art.TCCCompra = tcCCompra
                lista.Add(art)
            End While

            Dim i As Integer = 0

            Dim articulos(lista.Count - 1) As Articulo

            While lista.Count > i
                articulos(i) = lista(i)
                i = i + 1
            End While

            ' Call Close when done reading.
            reader.Close()
            Return articulos
        End Using

        Return Nothing

    End Function

    Private Function tipoCambio(ByVal anio As String, ByVal mes As String, ByVal dia As String) As Double
        Dim cambio As Double
        cambio = 1
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\CFG\\CAMBIOS.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sSelect As String = "SELECT V" & dia & " FROM CAMBIOS.DBF WHERE ANO = '" & anio & "' AND MES = '" & mes & "'"

        Using connection As New OdbcConnection(sConn)
            Dim command As New OdbcCommand(sSelect, connection)

            connection.Open()

            Dim reader As OdbcDataReader = command.ExecuteReader()
            While reader.Read()
                cambio = reader.GetDouble(0)
            End While

            If cambio = 0 Then
                cambio = 1
            End If

            reader.Close()
            Return cambio

        End Using

        Return cambio
    End Function

    Private Function obtenerMovimientos(ByVal cliente As String) As Double
        Dim anio As String = Format(Year(Now), "0").Substring(2, 2)
        Dim mes As String = Format(Month(Now), "00")
        Dim dia As String = FormatDateTime(Now(), DateFormat.ShortDate).Substring(0, 2)
        Dim tCambio As Double = tipoCambio(anio, mes, dia)

        Dim credito As Double = 0
        Dim decredito As Double = 0

        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\" & obtenerPeriodo() & "\\ARPENDSA.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sSelect As String = "SELECT codmat, MND, Treg, SUM(MONTO1) as MONTO " & _
        "FROM ARPENDSA.DBF WHERE codmat is not null and codmat = '" & cliente & "' " & _
        "GROUP BY codmat, MND, Treg"

        Dim cambio As Double = 1
        Using connection As New OdbcConnection(sConn)
            Dim command As New OdbcCommand(sSelect, connection)
            connection.Open()
            Dim reader As OdbcDataReader = command.ExecuteReader()

            While reader.Read()
                cambio = 1
                If reader.GetInt16(1) = 1 Then
                    cambio = tCambio
                End If
                If reader.GetInt16(2) = 1 Then
                    credito = credito + reader.GetDouble(3) * cambio
                Else
                    decredito = decredito + reader.GetDouble(3) * cambio
                End If

            End While
            credito = Math.Round(credito, 2)
            decredito = Math.Round(decredito, 2)
            reader.Close()
        End Using

        Return credito - decredito
    End Function

    Private Function pedidoEnTransitoGrupo(ByVal cliente As String) As Double
        Dim periodo As String = obtenerPeriodo()
        Dim anio As String = Format(Year(Now), "0").Substring(2, 2)
        Dim mes As String = Format(Month(Now), "00")
        Dim dia As String = FormatDateTime(Now(), DateFormat.ShortDate).Substring(0, 2)
        Dim tCambio As Double = tipoCambio(anio, mes, dia)
        Dim cambio As Double = 1

        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRH01.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim campo As String = "comision"
        Dim sSelect As String = "SELECT docto, moneda, sum(totfac) FROM ctrh01 WHERE status in ('C','D','G','F') and codmat='" & cliente & "' group by docto, moneda"

        Dim pedidoEnTransito As Double = 0
        Using connection As New OdbcConnection(sConn)
            connection.Open()
            Dim command As New OdbcCommand(sSelect, connection)
            Dim reader As OdbcDataReader = command.ExecuteReader()
            While reader.Read()
                cambio = 1
                If reader.GetString(1).Trim = "1" Then
                    cambio = tCambio
                End If
                pedidoEnTransito = pedidoEnTransito + (reader.GetDouble(2) * cambio)
            End While
            reader.Close()
        End Using

        Return pedidoEnTransito
    End Function

    Private Function datosGrupo(ByVal cliente As Cliente) As Cliente
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\TABLAS\\ARCLIENT.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sSelect As String = "SELECT cli.codmat, sum(iif(empty(credasig),0,credasig)*(100+" & cliente.CLI_PorcentajeSobregiro & ")/100) as 'CreditoSobreGiro'," & _
        " max(iif(mfvence='S','1','0')) as mfvence, max(iif(mlxacep='S','1','0')) as mlxacep, " & _
        " max(iif(mlprote='S','1','0')) as mlprote, max(iif(lsindis='S','1','0')) as lsindis, max(iif(bmanual='S','1','0')) as bmanual, " & _
        " sum(iif(empty(credasig),0,credasig)), count(*) " & _
        " FROM ARCLIENT cli " & _
        " WHERE cli.codmat='" & cliente.CLI_CodigoMadre & "'" & _
        " GROUP BY cli.codmat"

        'Dim deudaClienteGrupo As Double = 0
        Using connection As New OdbcConnection(sConn)
            connection.Open()
            Dim command As New OdbcCommand(sSelect, connection)
            Dim reader As OdbcDataReader = command.ExecuteReader()
            Dim deudaClienteGrupo As Double = obtenerMovimientos(cliente.CLI_CodigoMadre)
            While reader.Read()
                cliente.CLI_LineaCredito = reader.GetDouble(7)
                cliente.CLI_PedidosPendientes = pedidoEnTransitoGrupo(cliente.CLI_CodigoMadre)
                cliente.CLI_PedidosPendientes = 0
                cliente.CLI_SaldoLCredito = Math.Round(reader.GetDouble(7) - deudaClienteGrupo - cliente.CLI_PedidosPendientes, 2)
                cliente.CLI_LCreditoSobregiro = Math.Round(reader.GetDecimal(1), 2)
                cliente.CLI_SaldoConSobregiro = Math.Round(reader.GetDecimal(1) - deudaClienteGrupo - cliente.CLI_PedidosPendientes, 2)

                cliente.CLI_Bloqueado = "0"
                If reader.GetString(2).Trim = "1" Or reader.GetString(3).Trim = "1" Or reader.GetString(4).Trim = "1" Or reader.GetString(5).Trim = "1" Or reader.GetString(6).Trim = "1" Then
                    cliente.CLI_Bloqueado = "1"
                End If
                If reader.GetString(2).Trim = "1" Then
                    cliente.CLI_MFVence = "S"
                Else
                    cliente.CLI_MFVence = ""
                End If
                If reader.GetString(3).Trim = "1" Then
                    cliente.CLI_MLXAcep = "S"
                Else
                    cliente.CLI_MLXAcep = ""
                End If
                If reader.GetString(4).Trim = "1" Then
                    cliente.CLI_MLProte = "S"
                Else
                    cliente.CLI_MLProte = ""
                End If
                If reader.GetString(5).Trim = "1" Then
                    cliente.CLI_LSindis = "S"
                Else
                    cliente.CLI_LSindis = ""
                End If
                If reader.GetString(6).Trim = "1" Then
                    cliente.CLI_BManual = "S"
                Else
                    cliente.CLI_BManual = ""
                End If

                cliente.CLI_CantVinculados = reader.GetDouble(8) - 1
            End While

            ' Call Close when done reading.
            reader.Close()
        End Using

        Return cliente
    End Function

    Function obtenerCliente(codigo As String) As Cliente Implements IService1.obtenerCliente
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\TABLAS\\ARCLIENT.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sSelect As String = "SELECT cli.Codigo as CLI_Codigo, cli.Nom1 as CLI_Descripcion, cli.NroRuc, cli.LE, cli.CodVen as VEN_Codigo, " & _
        " cli.CodCob as COB_Codigo, cli.CredAsig as CLI_LineaCredito, cli.ClienFec as CLI_ClienteDesde, cli.Tele as CLI_Telefono, cli.Fax as CLI_Fax, cli.Cpos as DIS_Codigo, cli.Dir1 as CLI_DireccionFiscal," & _
        " cli.Dir2 as CLI_DireccionEntMerc, cli.Dir3 as CLI_DireccionCobranza, cli.Repre as CLI_Propietario,  cli.codmat, cli.vendor, cli.nom2, cli.cpos2, cli.retencion, cli.reintegro, cli.infofax, cli.catcli, " & _
        " cli.tipo , cli.marca, tip.factor, percepcion, parcial " & _
        " FROM ARCLIENT cli " & _
        " INNER JOIN INFOGEN tip ON code+subcode='CC01' and tip.item = cli.catcli" & _
        " WHERE cli.Codigo ='" & codigo & "'"
        ', CREDASIG
        Using connection As New OdbcConnection(sConn)
            Dim command As New OdbcCommand(sSelect, connection)

            connection.Open()

            Dim reader As OdbcDataReader = command.ExecuteReader()
            Dim cliente As New Cliente
            While reader.Read()

                cliente.CLI_Codigo = reader.GetString(0).Trim
                cliente.CLI_Descripcion = reader.GetString(1).Trim.Replace("'", " ")

                cliente.CLI_RUC = reader.GetString(2).Trim
                If cliente.CLI_RUC.Trim = "" Then
                    cliente.CLI_RUC = ""
                End If

                cliente.CLI_LE = reader.GetString(3).Trim
                If cliente.CLI_LE.Trim = "" Then
                    cliente.CLI_LE = ""
                End If

                cliente.VEN_Codigo = reader.GetString(4).Trim
                cliente.COB_Codigo = reader.GetString(5).Trim
                If cliente.COB_Codigo.Trim = "" Then
                    cliente.COB_Codigo = ""
                End If
                cliente.CLI_LineaCredito = reader.GetDouble(6)
                If reader.GetDouble(6).ToString.Trim = "" Then
                    cliente.CLI_LineaCredito = ""
                End If
                cliente.CLI_ClienteDesde = reader.GetDateTime(7)
                If cliente.CLI_ClienteDesde.Trim = "" Then
                    cliente.CLI_ClienteDesde = Nothing
                End If
                cliente.CLI_Telefono = reader.GetString(8).Trim
                If cliente.CLI_Telefono.Trim = "" Then
                    cliente.CLI_Telefono = ""
                End If
                cliente.CLI_Fax = reader.GetString(9).Trim
                If cliente.CLI_Fax.Trim = "" Then
                    cliente.CLI_Fax = ""
                End If
                cliente.DIS_Codigo = reader.GetString(10).Trim
                If cliente.DIS_Codigo.Trim = "" Then
                    cliente.DIS_Codigo = ""
                End If
                cliente.CLI_DireccionFiscal = reader.GetString(11).Trim.Replace("'", " ")
                If cliente.CLI_DireccionFiscal.Trim = "" Then
                    cliente.CLI_DireccionFiscal = ""
                End If
                cliente.CLI_DireccionEntMerc = reader.GetString(12).Trim.Replace("'", " ")
                If cliente.CLI_DireccionEntMerc.Trim = "" Then
                    cliente.CLI_DireccionEntMerc = ""
                End If
                cliente.CLI_DireccionCobranza = reader.GetString(13).Trim.Replace("'", " ")
                If cliente.CLI_DireccionCobranza.Trim = "" Then
                    cliente.CLI_DireccionCobranza = ""
                End If
                cliente.CLI_Propietario = reader.GetString(14).Trim
                If cliente.CLI_Propietario.Trim = "" Then
                    cliente.CLI_Propietario = ""
                End If

                cliente.CLI_CodigoMadre = reader.GetString(15).Trim
                cliente.CLI_CodRepre = reader.GetString(16).Trim
                cliente.CLI_Descripcion2 = reader.GetString(17).Trim.Replace("'", " ")
                cliente.DIS_Codigo2 = reader.GetString(18).Trim
                cliente.CLI_Retencion = reader.GetString(19).Trim
                cliente.CLI_Reintegro = reader.GetString(20).Trim
                cliente.CLI_Infofax = reader.GetString(21).Trim
                cliente.CLI_Catcli = reader.GetString(22).Trim
                cliente.CLI_LimPro = reader.GetString(23).Trim
                cliente.CLI_Marca = reader.GetString(24).Trim
                cliente.CLI_PorcentajeSobregiro = reader.GetDouble(25)
                cliente.CLI_Percepcion = reader.GetString(26)
                cliente.CLI_DParcial = reader.GetString(27)
            End While
            reader.Close()

            cliente = datosGrupo(cliente)

            Return cliente

        End Using
    End Function

    Function obtenerCondicionVenta(cnvCodigo As String) As CondicionVenta Implements IService1.obtenerCondicionVenta
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\TABLAS\\INFOGEN.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sSelect As String = "SELECT item, descrip, '1' FROM INFOGEN WHERE code+subcode = '0902' AND item = '" & cnvCodigo & "'"

        ', CREDASIG
        Using connection As New OdbcConnection(sConn)
            Dim command As New OdbcCommand(sSelect, connection)

            connection.Open()

            Dim reader As OdbcDataReader = command.ExecuteReader()
            Dim ConVen As New CondicionVenta
            While reader.Read()
                ConVen.CNV_Codigo = reader.GetString(0).Trim
                ConVen.CNV_Descripcion = reader.GetString(1).Trim
                ConVen.CNV_Activo = reader.GetString(2).Trim
            End While
            reader.Close()

            Return ConVen
        End Using
    End Function

    Function obtenerFormaPago(fopCodigo As String) As FormaPago Implements IService1.obtenerFormaPago
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\TABLAS\\INFOGEN.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sSelect As String = "SELECT ITEM, DESCRIP, ABREV, FACTOR, PRICE, DOMI FROM INFOGEN.DBF " & _
        "WHERE CODE+SUBCODE='0601' and ITEM IS not null and DESCRIP IS not null AND ABREV IS not null and ITEM='" & fopCodigo & "'"

        Using connection As New OdbcConnection(sConn)
            connection.Open()
            Dim command As New OdbcCommand(sSelect, connection)
            Dim reader As OdbcDataReader = command.ExecuteReader()

            Dim condicion As New FormaPago
            While reader.Read()

                condicion.FOP_Codigo = reader.GetString(0).Trim
                condicion.FOP_Descripcion = reader.GetString(1).Trim

                condicion.CNV_Codigo = reader.GetString(2).Trim
                If reader.GetInt32(3) Then
                    condicion.FOP_Dias = reader.GetInt32(3)
                Else
                    condicion.FOP_Dias = 0
                End If

                condicion.FOP_Descuentos = reader.GetDouble(4)
                condicion.FOP_ObligaCreditos = reader.GetString(5).Trim
                condicion.FOP_Activo = "1"
            End While

            ' Call Close when done reading.
            reader.Close()

            Return condicion

        End Using
    End Function

    Function obtenerMoneda(monCodigo As String) As Moneda Implements IService1.obtenerMoneda
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\TABLAS\\INFOGEN.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sSelect As String = "SELECT item, abrev1, descrip, abrev, '1' FROM INFOGEN WHERE code+subcode = '1001' AND item = '" & monCodigo & "'"

        Using connection As New OdbcConnection(sConn)
            connection.Open()
            Dim command As New OdbcCommand(sSelect, connection)
            Dim reader As OdbcDataReader = command.ExecuteReader()

            Dim moneda As New Moneda
            While reader.Read()

                moneda.MON_Codigo = reader.GetString(0).Trim
                moneda.MON_CodigoSIS = reader.GetString(1).Trim
                moneda.MON_Descripcion = reader.GetString(2).Trim
                moneda.MON_Abreviatura = reader.GetString(3).Trim
                moneda.MON_Activo = reader.GetString(4).Trim
            End While

            ' Call Close when done reading.
            reader.Close()

            Return moneda

        End Using
    End Function

    Function obtenerVendedor(venCodigo As String) As Vendedor Implements IService1.obtenerVendedor
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\TABLAS\\INFOGEN.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sSelect As String = "SELECT item, ruc, descrip, descrip2, item2, abrev, nroped, '1' FROM INFOGEN WHERE code+subcode='VNDD' and ITEM='" & venCodigo.ToUpper & "'"
        Dim vend As New Vendedor

        Using connection As New OdbcConnection(sConn)
            connection.Open()
            Dim command As New OdbcCommand(sSelect, connection)
            Dim reader As OdbcDataReader = command.ExecuteReader()
            While reader.Read()
                vend.VEN_CIA = "01"
                vend.VEN_Codigo = reader.GetString(0).Trim
                vend.VEN_DNI = reader.GetString(1).Trim
                vend.VEN_Descripcion = reader.GetString(2).Trim
                vend.VEN_DesCorta = reader.GetString(3).Trim
                vend.VEN_Orden = reader.GetString(4).Trim
                vend.VEN_Grupo = reader.GetString(5).Trim
                vend.VEN_CorrPedido = reader.GetString(6).Trim
                vend.VEN_Activo = reader.GetString(7).Trim
            End While

            ' Call Close when done reading.
            reader.Close()
            'Try
            'Catch ex As Exception
            '    Return Nothing
            'End Try

        End Using
        Return vend
    End Function

    Function obtenerTipoDocumento(tdocCodigo As String) As TipoDocumento Implements IService1.obtenerTipoDocumento
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\TABLAS\\INFOGEN.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sSelect As String = "SELECT item, descrip, abrev, '0', factor FROM INFOGEN WHERE code+subcode='TD01' and ITEM='" & tdocCodigo & "'"
        Dim tipoDoc As New TipoDocumento

        Using connection As New OdbcConnection(sConn)
            connection.Open()
            Dim command As New OdbcCommand(sSelect, connection)
            Dim reader As OdbcDataReader = command.ExecuteReader()
            While reader.Read()
                tipoDoc.TDOC_Codigo = reader.GetString(0).Trim
                tipoDoc.TDOC_Descripcion = reader.GetString(1).Trim
                tipoDoc.TDOC_Abreviatura = reader.GetString(2).Trim
                tipoDoc.TDOC_Venta = reader.GetString(3).Trim
                tipoDoc.TDOC_Factor = reader.GetString(4).Trim
            End While

            ' Call Close when done reading.
            reader.Close()
            'Try
            'Catch ex As Exception
            '    Return Nothing
            'End Try

        End Using
        Return tipoDoc
    End Function

    Function obtenerUbigeo(codDistrito As String) As Distrito Implements IService1.obtenerUbigeo
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\TABLAS\\INFOGEN.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sSelect As String = "SELECT item2, descrip, provi, distri FROM INFOGEN WHERE code+subcode='0701' and ITEM2='" & codDistrito & "'"
        Dim distrito As New Distrito

        Using connection As New OdbcConnection(sConn)
            connection.Open()
            Dim command As New OdbcCommand(sSelect, connection)
            Dim reader As OdbcDataReader = command.ExecuteReader()
            While reader.Read()
                distrito.DIS_Codigo = reader.GetString(0).Trim
                distrito.DEP_Descripcion = reader.GetString(1).Trim
                distrito.PRO_Descripcion = reader.GetString(2).Trim
                distrito.DIS_Descripcion = reader.GetString(3).Trim
            End While
            reader.Close()
        End Using

        Return distrito

    End Function

    Function obtenerDocumentoCliente(nroPedido As String) As DocumentoCliente Implements IService1.obtenerDocumentoCliente
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRH01.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sSelect As String = "SELECT docto,fecha,cliente,vendor,mnd,docfac,tventa,atransx,diver2,descto2,diver3,imp,tpedido,fchpro,obs1 + ', ' + obs2 as observaciones,detalle,direcc2,status,liprec FROM CTRH01 " &
                                 "WHERE docto = '" & nroPedido.ToUpper & "'"

        Dim estado As String = ""
        Dim pedido As New DocumentoCliente

        Using connection As New OdbcConnection(sConn)

            connection.Open()
            Dim command As New OdbcCommand(sSelect, connection)
            Dim reader As OdbcDataReader = command.ExecuteReader()

            If reader.Read() Then
                pedido.CIA = "01"
                pedido.SUC_Codigo = "01"
                pedido.TDOC_Codigo = "P"
                pedido.DCI_Codigo = reader.GetString(0).Trim
                pedido.DCI_Fecha = reader.GetString(1).Trim
                pedido.CLI_Codigo = reader.GetString(2).Trim
                pedido.VEN_Codigo = reader.GetString(3).Trim
                pedido.MON_Codigo = reader.GetString(4).Trim
                pedido.TDO_Codigo = reader.GetString(5).Trim
                pedido.FOP_Codigo = reader.GetString(6).Trim
                pedido.AGE_Codigo = reader.GetString(7).Trim
                pedido.DCI_TotalBruto = reader.GetString(8).Trim
                pedido.DCI_TotalDsct = reader.GetString(9).Trim
                pedido.DCI_TotalNeto = reader.GetString(10).Trim
                pedido.DCI_IGV = reader.GetString(11).Trim
                pedido.DCI_Total = reader.GetString(12).Trim
                pedido.DCI_UltModificacion = reader.GetString(13).Trim
                pedido.DCI_Observaciones = reader.GetString(14).Trim
                pedido.DCI_ObsCredito = reader.GetString(15).Trim
                pedido.DCI_DireccionEntrega = reader.GetString(16).Trim
                pedido.DCI_Estado = reader.GetString(17).Trim
                pedido.DCI_LPrecio = reader.GetString(18).Trim
            End If

            reader.Close()
        End Using

        Return pedido

    End Function

    Function obtenerCorrelativoVendedor(ByVal codVendedor As String) As Integer Implements IService1.obtenerCorrelativoVendedor
        Dim vendedor As New Vendedor
        vendedor = obtenerVendedor(codVendedor.ToUpper())
        Dim correlativo = vendedor.VEN_CorrPedido + 1
        Return correlativo
    End Function

    Function actualizarCorrelativoVendedor(ByVal codVendedor As String) As Boolean Implements IService1.actualizarCorrelativoVendedor
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\TABLAS\\INFOGEN.DBF"
        Dim sConn As String
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sqlComm As OdbcCommand
        Dim Resultado As Integer
        Dim sql As String

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()

                sql = "UPDATE INFOGEN SET Nroped=Nroped+1 WHERE code+subcode='VNDD' and item = '" & codVendedor.ToUpper() & "'"
                sqlComm = New OdbcCommand(sql, dbConn)
                Resultado = sqlComm.ExecuteNonQuery()

                dbConn.Close()
            Catch ex As Exception
                Return False
            End Try

        End Using
        Return True
    End Function

    Function obtenerStockArticulo(ByVal codArticulo As String) As Double Implements IService1.obtenerStockArticulo
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\" + obtenerPeriodo() + "\\INVMAST.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sSelect As String = "SELECT mano-compr FROM INVMAST WHERE item='" + codArticulo + "' and ITEM IS not null and DESCRIP IS not null"

        Using connection As New OdbcConnection(sConn)

            connection.Open()
            Dim command As New OdbcCommand(sSelect, connection)

            Dim reader As OdbcDataReader = command.ExecuteReader()
            'ARRAY LIST
            Dim stock As Double = 0

            While reader.Read()
                stock = reader.GetDouble(0)
            End While

            Return stock

        End Using
    End Function

    Function registrarDocumentoCliente(ByVal nroPedido As String, ByVal codCliente As String, ByVal fechaPedido As String, ByVal horaPedido As String, _
        ByVal condicion As String, ByVal docEmision As String, ByVal moneda As String, ByVal listaPrecio As String) As Boolean Implements IService1.registrarDocumentoCliente
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRH01.DBF"
        Dim sqlComm As OdbcCommand
        Dim Resultado As Integer
        Dim mensaje As String = ""

        Dim cliente As New Cliente
        Dim condPago As New FormaPago
        cliente = obtenerCliente(codCliente)
        condPago = obtenerFormaPago(condicion)

        Dim anio As String = Format(Year(Now), "0").Substring(2, 2)
        Dim mes As String = Format(Month(Now), "00")
        Dim dia As String = FormatDateTime(Now(), DateFormat.ShortDate).Substring(0, 2)
        Dim tCambio As Double = tipoCambio(anio, mes, dia)
        Dim periodo As String = obtenerPeriodo()
        Dim mesPeriodo As Integer = Integer.Parse(periodo.Substring(2, 2))

        Dim sConn As String
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sql As String

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                sql = "INSERT INTO CTRH01( DOCTO,DOCTO2,TALON,REFER,SW1,NOC,STATUS,CPOS,TDOC,TREG,FECHA,FECHA1,FECHA2,FECHA3,HORA1, " & _
                "CLIENTE,CODMAT,NOMBRE,NOM2,DIRECC1,DIRECC2,DIRECC3,TELEFONO,ANEXO,CPAGO,VENDOR,VENDOR2,COMIS,TIENDA,AGENCIA,DESCUE,MONEDA,CAMBIO,PERIODO,GL, " & _
                "IMP,TRANSP,DIVER1,DIVER2,MND,DIVER3,TOTFAC,IMPCOM,PREPAG,NCHEQUE,DESCTO1,DESCTO2,DESCTO3,TRFS,TRCODI,TRANSPT,TRDOMI,TRRUC,TRPLACA,LETRAS," & _
                "LET_NRO1,LET_VTO1,LET_IMP1,LET_DIA1,LET_COM1,LET_NRO2,LET_VTO2,LET_IMP2,LET_DIA2,LET_COM2,LET_NRO3,LET_VTO3,LET_IMP3,LET_DIA3,LET_COM3, " & _
                "LET_NRO4,LET_VTO4,LET_IMP4,LET_DIA4,LET_COM4,LET_NRO5,LET_VTO5,LET_IMP5,LET_DIA5,LET_COM5,LET_NRO6,LET_VTO6,LET_IMP6,LET_DIA6,LET_COM6, " & _
                "LET_NRO7,LET_VTO7,LET_IMP7,LET_DIA7,LET_COM7,LET_NRO8,LET_VTO8,LET_IMP8,LET_DIA8,LET_COM8,LET_NRO9,LET_VTO9,LET_IMP9,LET_DIA9,LET_COM9, " & _
                "LET_NRO10,LET_VTO10,LET_IMP10,LET_DIA10,LET_COM10,LET_NRO11,LET_VTO11,LET_IMP11,LET_DIA11,LET_COM11,LET_NRO12,LET_VTO12,LET_IMP12, " & _
                "LET_DIA12,LET_COM12,LET_NRO13,LET_VTO13,LET_IMP13,LET_DIA13,LET_COM13,LET_NRO14,LET_VTO14,LET_IMP14,LET_DIA14,LET_COM14,LET_NRO15, " & _
                "LET_VTO15,LET_IMP15,LET_DIA15,LET_COM15,LET_NRO16,LET_VTO16,LET_IMP16,LET_DIA16,LET_COM16,LET_NRO17,LET_VTO17,LET_IMP17,LET_DIA17, " & _
                "LET_COM17,LET_NRO18,LET_VTO18,LET_IMP18,LET_DIA18,LET_COM18,LET_NRO19,LET_VTO19,LET_IMP19,LET_DIA19,LET_COM19,LET_NRO20,LET_VTO20, " & _
                "LET_IMP20,LET_DIA20,LET_COM20,FECHA4,CVENTA,TVENTA,TURNOM1,TURNOM2,TURNOT1,TURNOT2,ZONA,RUTA,FECHA5,FECHA6,BANCO,BANCOX,DBANCOX, " & _
                "AGENCIAX,ATRANSX,DATRANSX,CODIUSER,ENVASE,GUIAS,LIPREC,DETALLE,NRORUC,LE,CPOS2,RETENCION,REINTEGRO,INFOFAX,OBS1,OBS2,FLGINT,FLGANT, " & _
                "CALPRE,TPEDIDO,NOMARCH,CANCELA,APROBO,DOCFAC,CATCLI, TASAPER, PERCEPCION, TOTCOBRAR, DOCPERCEP, CODSUC,CODUSU,FCHPRO, HORPRO, ESTPRO,PASPRO ) " & _
                "VALUES ( " & _
               " '" & nroPedido.ToUpper() & "', '',0,'','','','I','" & cliente.DIS_Codigo & "', '', 0, {" & fechaPedido & "}, {" & fechaPedido & "}, {" & fechaPedido & "}, {}, '" & horaPedido & "', " & _
                " '" & cliente.CLI_Codigo & "', '" & cliente.CLI_CodigoMadre & "', '" & cliente.CLI_Descripcion & "', '" & cliente.CLI_Descripcion2 & "', " & _
                " '" & cliente.CLI_DireccionFiscal & "', '" & cliente.CLI_DireccionEntMerc & "', '" & cliente.CLI_DireccionCobranza & "', '" & cliente.CLI_Telefono & "' , " & _
                " 0, '', '" & cliente.CLI_CodRepre & "', '', 0, '', " & _
                "'', 0, '" & moneda & "', " & tCambio & ", " & mesPeriodo & ", '', 0, 0, 0, 0, '" & moneda & "', 0, 0, " & _
                " 0, 0, '', 0, 0, 0, 0, '', '', {}, " & _
                " '', '', 0, '', {}, 0, 0, 0, '', {}, 0, 0, 0, '', {}, 0, 0, 0, '', {}, 0, 0, 0, '', {}, 0, 0, 0, '', {}, 0, 0, 0, ''," & _
                " {}, 0, 0, 0, '', {}, 0, 0, 0, '', {}, 0, 0, 0, '', {}, 0, 0, 0, '', {}, 0, 0, 0, '', {}, 0, 0, 0, '', {}, " & _
                " 0, 0, 0, '', {}, 0, 0, 0, '', {}, 0, 0, 0, '', {}, 0, 0, 0, '', {}, 0, 0, 0, '', {}, 0, 0, 0, '', {}, 0, 0, 0, '', {}, " & _
                " 0, 0, 0, {}, '" + condicion + "', '', '', '', '', '', '', '', {}, {}, '', '', '', '', '', " & _
                " '', '99', '', '', '" & listaPrecio & "', '', '" & cliente.CLI_RUC & "', '" & cliente.CLI_LE & "', " & _
                " '" & cliente.DIS_Codigo2 & "', '" & cliente.CLI_Retencion & "', '" & cliente.CLI_Reintegro & "', '" & cliente.CLI_Infofax & "', '', '', " & _
                " 'S', '', 'S', 0, '', '', '', '" & docEmision & "' , '" & cliente.CLI_Catcli & "',0,0,0,'', '', 99, {" & fechaPedido & "}, '" & DateTime.Now().ToString("HH:mm:ss") & "' ,'C', '' )"
                sqlComm = New OdbcCommand(sql, dbConn)
                Resultado = sqlComm.ExecuteNonQuery()


                sql = "INSERT INTO CTRREP01 (VENDOR,MERC,FECHA,HORA,FECHAC,HORAC,FECHAA,HORAA,FECHAD,HORAD,FECHAG,HORAG,FECHAF," & _
                "HORAF,FECHADP,HORSAL,HORENT,PEDIDO,CLIENTE,CODMAT,NOMBRE,MONTO,LETRA,OBSERV,OBSERV1,TVENTA,ARCHIVO,FLGINT,FLGANT," & _
                "CALPRE,TPEDIDO,NOMARCH,HSALIDA,VEHICULO,CODSUC,CODUSU,FCHPRO, HORPRO, ESTPRO, PASPRO)" & _
                " VALUES( " & _
                "'" & nroPedido.Substring(0, 2).ToUpper() & "', '1', {" & fechaPedido & "}, '" & horaPedido & "', {}, '', {}, '', {}, '', {},'', {}, '', {}, '','','" & nroPedido.ToUpper() & "', " & _
                "'" & cliente.CLI_Codigo & "','" & cliente.CLI_CodigoMadre & "','" & cliente.CLI_Descripcion & "',0,0,'','','',{},'S',''," & _
                "'',0,'','','','',99, {" & fechaPedido & "},'" & DateTime.Now().ToString("HH:mm:ss") & "','C','')"
                sqlComm = New OdbcCommand(sql, dbConn)
                Resultado = sqlComm.ExecuteNonQuery()

                actualizarCorrelativoVendedor(nroPedido.Substring(0, 2).ToUpper())
                dbConn.Close()

            Catch ex As Exception
                Return False
                'Return ex.Message
            End Try
        End Using

        'Return mensaje
        Return True
    End Function

    Function obtenerNvoPrecio(ByVal arti As Articulo, ByVal listaPrecio As String, ByVal moneda As String) As Double Implements IService1.obtenerNvoPrecio
        Dim precio As Double = 1
        Dim anio As String = Format(Year(Now), "0")
        Dim mes As String = Format(Month(Now), "00")
        Dim tcCVta As Double = tcComercialVenta(anio, mes)
        Dim tcCCompra As Double = tcComercialCompra(anio, mes)
        Dim cambio As Double = 1

        'Dim arti As New Articulo
        'arti = obtenerArticulo(Articulo)

        If listaPrecio = "1" Then
            precio = arti.ART_LPrecio1
            If arti.ART_MLPrecio1 = "0" And moneda = "1" Then
                precio = arti.ART_LPrecio1 / tcCCompra
            ElseIf arti.ART_MLPrecio1 = "1" And moneda = "0" Then
                precio = arti.ART_LPrecio1 * tcCVta
            End If
        ElseIf listaPrecio = "2" Then
            precio = arti.ART_LPrecio2
            If arti.ART_MLPrecio2 = "0" And moneda = "1" Then
                precio = arti.ART_LPrecio2 / tcCCompra
            ElseIf arti.ART_MLPrecio2 = "1" And moneda = "0" Then
                precio = arti.ART_LPrecio2 * tcCVta
            End If
        Else
            precio = arti.ART_LPrecio3
            If arti.ART_MLPrecio3 = "0" And moneda = "1" Then
                precio = arti.ART_LPrecio3 / tcCCompra
            ElseIf arti.ART_MLPrecio3 = "1" And moneda = "0" Then
                precio = arti.ART_LPrecio3 * tcCVta
            End If
        End If
        Return Math.Round(precio, 2)
    End Function

    Function obtenerDsto(ByVal articulo As String, ByVal cantidad As Double) As Double Implements IService1.obtenerDsto
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\TABLAS\\DESCUENT.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sSelect As String = "SELECT descuento FROM DESCUENT WHERE codArti='" & articulo & "' and cantidad<=" & cantidad & " ORDER BY cantidad desc"
        Dim descuento As Double = 0D
        Using connection As New OdbcConnection(sConn)
            connection.Open()
            Dim command As New OdbcCommand(sSelect, connection)
            Dim reader As OdbcDataReader = command.ExecuteReader()
            If reader.Read() Then
                descuento = Math.Round(reader.GetDecimal(0), 4)
            End If
            reader.Close()
        End Using

        Return Math.Round(descuento * 100, 2)
    End Function

    Private Function acumularDocumentoCliente(ByVal nroPedido As String) As Boolean
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRD01.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sSelect As String = "SELECT docto, round(sum(lista*qenv),2) as 'Total_Bruto', " & _
        " sum(round((round((round((lista*qenv),2)*desc1),2)/100),2)) as 'Dscto1', " & _
        " sum(round((round((round(round((round((round((lista*qenv),2)*(100-desc1)),2)/100),2),2)*desc2),2)/100),2)) as 'Dscto2', " & _
        " sum(round(round((lista*qenv),2) - round((round((round((lista*qenv),2)*desc1),2)/100),2) - round((round((round(round((round((round((lista*qenv),2)*(100-desc1)),2)/100),2),2)*desc2),2)/100),2),2)) as 'Total_Neto', " & _
        " sum(round(round(round((lista*qenv),2) - round((round((round((lista*qenv),2)*desc1),2)/100),2) - round((round((round(round((round((round((lista*qenv),2)*(100-desc1)),2)/100),2),2)*desc2),2)/100),2),2)*(1+(imp/100)),2)) as 'Total_Documento', " & _
        " round(sum(lista*qord),2) as 'Total_Pedido', " & _
        " sum(round(round(round((lista*qenv),2) - round((round((round((lista*qenv),2)*desc1),2)/100),2) - round((round((round(round((round((round((lista*qenv),2)*(100-desc1)),2)/100),2),2)*desc2),2)/100),2),2)*(imp/100),2)) as 'igv', " & _
        " round(sum(round(round((lista*qenv),2) - round((round((round((lista*qenv),2)*desc1),2)/100),2) - round((round((round(round((round((round((lista*qenv),2)*(100-desc1)),2)/100),2),2)*desc2),2)/100),2),2)*(comven/100)),2) as 'comision', " & _
        " sum(round( round(round(round((lista*qenv),2) - round((round((round((lista*qenv),2)*desc1),2)/100),2) - round((round((round(round((round((round((lista*qenv),2)*(100-desc1)),2)/100),2),2)*desc2),2)/100),2),2)*(1+(imp/100)),2)* (tasaper/100),2)) as 'percepcion' " & _
        " FROM ctrd01 " & _
        " WHERE docto='" & nroPedido.ToUpper() & "' " & _
        " GROUP BY docto"
        Dim TotalBruto As Double = 0
        Dim TotalNeto As Double = 0
        Dim TotalDocumento As Double = 0
        Dim Dscto1 As Double = 0
        Dim Dscto2 As Double = 0
        Dim TotalPedido As Double = 0
        Dim igv As Double = 0
        Dim comision As Double = 0
        Dim percepcion As Double = 0
        Dim sqlComm As OdbcCommand
        Dim Resultado As Integer

        Using connection As New OdbcConnection(sConn)
            Try
                connection.Open()
                Dim command As New OdbcCommand(sSelect, connection)
                Dim reader As OdbcDataReader = command.ExecuteReader()
                While reader.Read()
                    TotalBruto = reader.GetDouble(1)
                    Dscto1 = reader.GetDouble(2)
                    Dscto2 = reader.GetDouble(3)
                    TotalNeto = reader.GetDouble(4)
                    TotalDocumento = reader.GetDouble(5)
                    TotalPedido = reader.GetDouble(6)
                    igv = reader.GetDouble(7)
                    comision = reader.GetDouble(8)
                    percepcion = reader.GetDouble(9)
                End While

                sSelect = "UPDATE CTRH01 SET DIVER2=" & TotalBruto & ", Percepcion=" & percepcion & ", DIVER3=" & TotalNeto & ", TOTFAC=" & TotalDocumento & ", totCobrar=" & (TotalDocumento + percepcion) & ", " & _
                " DESCTO1=" & Dscto1 & ", DESCTO2=" & Dscto2 & ", TPEDIDO=" & TotalPedido & ", imp=" & igv & ", IMPCOM=" & comision & " " & _
                " WHERE docto = '" & nroPedido.ToUpper() & "'"

                sqlComm = New OdbcCommand(sSelect, connection)
                Resultado = sqlComm.ExecuteNonQuery()
                ' Call Close when done reading.
                reader.Close()
            Catch ex As Exception
                Return False
            End Try
        End Using

        Return True
    End Function

    'Function registrarDocumentoClienteDetalle(ByVal nroPedido As String, ByVal solicitado As Double, ByVal atendido As Double, ByVal artCodigo As String, _
    '    ByVal descCondicion As Double, ByVal descuento As Double, ByVal regalo As String, ByVal precRegalo As String, ByVal vendedor As String, _
    '    ByVal tasaPercepcion As Double, ByVal moneda As String, ByVal listaPrecio As String) As Boolean Implements IService1.registrarPedidoDetalle
    '    If obtenerDocumentoCliente(nroPedido.ToUpper).DCI_Estado.ToUpper = "I" Then
    '        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRD01.DBF"
    '        Dim sqlComm As OdbcCommand
    '        Dim Resultado As Integer

    '        Dim periodo As String = obtenerPeriodo()
    '        Dim mesPeriodo As Integer = Integer.Parse(periodo.Substring(2, 2))

    '        Dim arti As New Articulo
    '        arti = obtenerArticulo(artCodigo)

    '        Dim precio As Double
    '        precio = obtenerNvoPrecio(arti, listaPrecio, moneda)
    '        'precio = obtenerNvoPrecio(artCodigo, listaPrecio, moneda)

    '        Dim sConn As String
    '        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
    '        Dim sql As String = ""

    '        Dim comision As Double = 0
    '        'comision = obtenerComision(arti.PROV_Codigo, vendedor.ToUpper())

    '        Dim excedeDscto As String = "0"
    '        If descuento > obtenerDsto(artCodigo, atendido) Then
    '            excedeDscto = "1"
    '        End If

    '        Dim pendiente As Double = (solicitado - atendido)
    '        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
    '            Try
    '                dbConn.Open()
    '                sql = "INSERT INTO CTRD01(DOCTO,DOCTO2,NUMGUI,FECGUI,ENTR,STATUS,TREG,QORD,QENV,QPEN,ITEM,CODALM,DESCRIP,UM,CLASE,COSTO, " & _
    '                " COSTOD,LISTA,DESC1,DESC2,DESC3,IMP,PERIODO,NCTA,ENV,ITEM1,CANT1,PREC1,TRFS,DESC,COMVEN,NUMDET,UBICA,FLGNA," & _
    '               " LARGO, TASAPER, CODSUC, CODUSU,FCHPRO, HORPRO,ESTPRO,PASPRO) " & _
    '                " VALUES(" & _
    '                "'" & nroPedido.ToUpper() & "','','',{},0,'',0," & solicitado & "," & atendido & "," & pendiente & ",'" & artCodigo & "','', " & _
    '                "'" & arti.ART_Descripcion & "', '" & arti.UND_Codigo & "', '" & arti.CLA_Codigo & "', " & arti.ART_Costos & _
    '                ", " & arti.ART_CostoD & ", " & precio & ", " & descCondicion & ", " & descuento & ", " & _
    '                "0, 18, " & mesPeriodo & ", '" & precRegalo & "', '" & regalo & "', '" & arti.PROV_Codigo & "', 0, 0, 0, " & _
    '                "'" + excedeDscto + "', " & comision & ", '', '" & arti.ART_Ubicacion & "', '', '" & arti.ART_Largo & "', " & tasaPercepcion & ", '',99, {" & Date.Now.ToString("MM/dd/yy") & "}, '" & DateTime.Now().ToString("HH:mm:ss") & "', 'C', '')"
    '                sqlComm = New OdbcCommand(sql, dbConn)
    '                Resultado = sqlComm.ExecuteNonQuery()

    '                sql = "UPDATE " & obtenerPeriodo() & "/INVMAST SET compr=compr+" & atendido & " WHERE ITEM = '" & artCodigo & "'"
    '                sqlComm = New OdbcCommand(sql, dbConn)
    '                Resultado = sqlComm.ExecuteNonQuery()

    '                dbConn.Close()

    '            Catch ex As Exception
    '                Return False
    '            End Try
    '        End Using
    '        acumularDocumentoCliente(nroPedido)
    '        Return True
    '    Else
    '        Return False
    '    End If
    'End Function

    Function eliminarDocumentoClienteDetalle(ByVal nroPedido As String, ByVal artCodigo As String, ByVal cantidad As Double) As Boolean Implements IService1.eliminarDocumentoClienteDetalle
        If obtenerDocumentoCliente(nroPedido.ToUpper).DCI_Estado.ToUpper = "I" Then
            Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRD01.DBF"
            Dim sConn As String
            sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
            Dim sqlComm As OdbcCommand
            Dim Resultado As Integer
            Dim sql As String

            Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
                Try
                    dbConn.Open()
                    sql = "DELETE FROM CTRD01 WHERE DOCTO='" & nroPedido.ToUpper() & "' and ITEM = '" & artCodigo & "'"
                    sqlComm = New OdbcCommand(sql, dbConn)
                    Resultado = sqlComm.ExecuteNonQuery()

                    sql = "UPDATE " & obtenerPeriodo() & "/INVMAST SET compr=compr-" & cantidad & " WHERE ITEM = '" & artCodigo & "'"
                    sqlComm = New OdbcCommand(sql, dbConn)
                    Resultado = sqlComm.ExecuteNonQuery()

                    dbConn.Close()

                Catch ex As Exception
                    Return False
                End Try

            End Using
            acumularDocumentoCliente(nroPedido)
        Else
            Return False
        End If

        Return True
    End Function

    Private Function confirmarImpresion(ByVal nroDetalle As String) As Boolean
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\ctrdet01.DBF"
        Dim sConn As String
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sqlComm As OdbcCommand
        Dim Resultado As Integer
        Dim sql As String

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                sql = "UPDATE ctrdet01 SET FLGIMP='1' WHERE NRODET='" & nroDetalle & "'"
                sqlComm = New OdbcCommand(sql, dbConn)
                Resultado = sqlComm.ExecuteNonQuery()
                dbConn.Close()

            Catch ex As Exception
                Return False
            End Try

        End Using
        Return True
    End Function

    Private Function eliminarAprobacion(ByVal nroPedido As String) As Boolean
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\ctrdet01.DBF"
        Dim sConn As String
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sqlComm As OdbcCommand
        Dim Resultado As Integer
        Dim sql As String

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                sql = "UPDATE ctrdet01 SET activo='0' WHERE NROPED='" & nroPedido.ToUpper() & "'"
                sqlComm = New OdbcCommand(sql, dbConn)
                Resultado = sqlComm.ExecuteNonQuery()

                sql = "DELETE FROM ctrdet01 WHERE NROPED='" & nroPedido.ToUpper() & "'"
                sqlComm = New OdbcCommand(sql, dbConn)
                Resultado = sqlComm.ExecuteNonQuery()

                sql = "DELETE FROM ctrdet02 WHERE NROPED='" & nroPedido.ToUpper() & "'"
                sqlComm = New OdbcCommand(sql, dbConn)
                Resultado = sqlComm.ExecuteNonQuery()

                sql = "DELETE FROM CTRAPROB WHERE NUMPED='" & nroPedido.ToUpper() & "'"
                sqlComm = New OdbcCommand(sql, dbConn)
                Resultado = sqlComm.ExecuteNonQuery()

                sql = "DELETE FROM CTRDETA WHERE PEDIDO='" & nroPedido.ToUpper() & "'"
                sqlComm = New OdbcCommand(sql, dbConn)
                Resultado = sqlComm.ExecuteNonQuery()

                dbConn.Close()

            Catch ex As Exception
                Return False
            End Try

        End Using
        Return True

    End Function

    Private Function registrarAprob(ByVal nroPedido As String, ByVal nroDetalle As String, ByVal fecha As String, ByVal hora As String, ByVal convta As String, _
        ByVal conpag As String, ByVal codven As String, ByVal mnd As String, ByVal valvta As Double, ByVal totfac As Double, _
        ByVal cliente As Cliente, ByVal aproAuto As String, ByVal totCobrar As Double) As Boolean
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRAPROB.DBF"
        Dim sqlComm As OdbcCommand
        Dim Resultado As Integer

        Dim vendedor As New Vendedor
        vendedor = obtenerVendedor(codven.ToUpper())

        Dim condic As New FormaPago
        condic = obtenerFormaPago(conpag)

        Dim sConn As String
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sql As String = ""

        Dim limpro As String = ""
        If cliente.CLI_LimPro = "1" Then
            limpro = "Lima"
        End If
        If cliente.CLI_LimPro = "2" Then
            limpro = "Provincia"
        End If

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                sql = "INSERT INTO CTRAPROB(FECHA,HORA,NUMPED,NUMDET,ANULADO,CODCLI,CODMAT,NOMCLI,CODUSU,NOMUSU,CALIFICA," & _
                "MOTDES,NOMDES,CATCLI,NOMCAT,LIMPROV,CODZON,DEPARTAME,PROVIN,DISTRI,CONVTA,CONPAG,NOMCON,CODVEN,NOMVEN," & _
                "MND,VALVTA,TOTFAC,APROAUTO,LCREDITO,LCREDSOB,PORSOBRE,DISPONIBLE,DISPCSOB,NUMVINCULO,TPXFACT,MFVENCE," & _
                "MLXACEP,MLPROTE,LSINDIS,BMANUAL,TBMANUAL, TOTCOBRAR,CODSUC,FCHPRO, HORPRO,ESTPRO,PASPRO)" & _
                " VALUES(" & _
                " {" & fecha & "}, '" & hora & "', '" & nroPedido.ToUpper() & "', '" & nroDetalle & "', '', '" & cliente.CLI_Codigo & "', '" & cliente.CLI_CodigoMadre & "'," & _
                " '" & cliente.CLI_Descripcion & "', 99, 'Sistema Internet', '', '', '', " & _
                " '" & cliente.CLI_Catcli & "', '', '" & limpro & "', '" & cliente.DIS_Codigo & "', '', '', '', '" & convta & "', " & _
                " '" & conpag & "', '" & condic.FOP_Descripcion & "', '" & codven & "', '" & vendedor.VEN_Descripcion & "', '" & mnd & "', " & valvta & ", " & totfac & ", '" & aproAuto & "', " & cliente.CLI_LineaCredito & ", " & _
                " " & cliente.CLI_LCreditoSobregiro & ", " & cliente.CLI_PorcentajeSobregiro & ", " & cliente.CLI_SaldoLCredito & ", " & cliente.CLI_SaldoConSobregiro & ", " & _
                " " & cliente.CLI_CantVinculados & ", " & cliente.CLI_CantVinculados & ", " & _
                " '" & cliente.CLI_MFVence & "', '" & cliente.CLI_MLXAcep & "', '" & cliente.CLI_MLProte & "', '" & cliente.CLI_LSindis & "', " & _
                " '" & cliente.CLI_BManual & "', '', 0,'', {" & fecha & "}, '" & DateTime.Now().ToString("HH:mm:ss") & "', 'C', '')"
                sqlComm = New OdbcCommand(sql, dbConn)
                Resultado = sqlComm.ExecuteNonQuery()

                dbConn.Close()

            Catch ex As Exception
                Return False
            End Try
        End Using

        Return True

    End Function

    Private Function actualizarCorrDetalle() As Boolean
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\TABLAS\\ARTRANS.DBF"
        Dim sConn As String
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sqlComm As OdbcCommand
        Dim Resultado As Integer
        Dim sql As String

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()

                sql = "UPDATE ARTRANS SET nro_sec=nro_sec+1 WHERE cod_mov = 'DETAIL'"
                sqlComm = New OdbcCommand(sql, dbConn)
                Resultado = sqlComm.ExecuteNonQuery()

                dbConn.Close()
            Catch ex As Exception
                Return False
            End Try

        End Using
        Return True
    End Function

    Private Function obtenerCorrDetalle() As Integer
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\TABLAS\\ARTRANS.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sSelect As String = "SELECT nro_sec+1 FROM ARTRANS.DBF WHERE cod_mov = 'DETAIL' "

        Dim correlativo As Integer = 1
        Using connection As New OdbcConnection(sConn)
            Dim command As New OdbcCommand(sSelect, connection)
            connection.Open()
            Dim reader As OdbcDataReader = command.ExecuteReader()

            While reader.Read()
                correlativo = reader.GetInt64(0)
            End While
            reader.Close()
        End Using

        Return correlativo
    End Function

    Private Function registrarCtrDet01(ByVal nroPedido As String, ByVal nroDetalle As String, ByVal cliente As Cliente, ByVal formaPago As String, _
    ByVal fechaPedido As String, ByVal codAge As String, ByVal obs1 As String, ByVal obs2 As String) As Boolean
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRDET01.DBF"
        Dim sqlComm As OdbcCommand
        Dim Resultado As Integer

        Dim agencia As New Agencia
        agencia = obtenerAgenciaTransporteDBF(codAge)

        Dim codDist As String = cliente.DIS_Codigo
        Dim entrega As String = cliente.CLI_DireccionEntMerc
        ' CLI_Limpro = 2 Es provincia
        If cliente.CLI_LimPro = "2" Then
            codDist = agencia.DIS_Codigo
            entrega = agencia.AGE_Descripcion
        End If

        Dim distrito As New Distrito
        distrito = obtenerUbigeo(codDist)

        Dim direccionFiscal As String = cliente.CLI_DireccionEntMerc
        Dim disCliente As Distrito = obtenerUbigeo(cliente.DIS_Codigo2)

        If disCliente.DIS_Descripcion = disCliente.PRO_Descripcion Then
            If disCliente.PRO_Descripcion = disCliente.DEP_Descripcion Then
                direccionFiscal = direccionFiscal & " " & disCliente.DIS_Descripcion
            Else
                direccionFiscal = direccionFiscal & " " & disCliente.DIS_Descripcion & " - " & disCliente.DEP_Descripcion
            End If
        Else
            direccionFiscal = direccionFiscal & " " & disCliente.DIS_Descripcion & " - " & disCliente.PRO_Descripcion & " - " & disCliente.DEP_Descripcion
        End If


        Dim sConn As String
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sql As String = ""

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()

                sql = "INSERT INTO CTRDET01(NROPED,NRODET,REINTE,DPARCI,CODCLI,CODMAT,FECDET,HORDET,NOMCLI1,NOMCLI2,DIRCLI,CODVTA, " & _
                " DIRENT,DISTRI,FECPED,MARCA,CODAGE,OBSER1,OBSER2,FECIMP,LIMPRO,TIPTRA,ESTCLI,HORIMP,FLGIMP,CANCELA,APROBO,CATCLI, ACTIVO, " & _
                " CODSUC,CODUSU,FCHPRO, HORPRO, ESTPRO,PASPRO) " & _
                "VALUES( " & _
                " '" & nroPedido.ToUpper() & "', '" & nroDetalle & "', '" & cliente.CLI_Reintegro & "', '" & cliente.CLI_Reintegro & "', '" & cliente.CLI_Codigo & "'," & _
                " '" & cliente.CLI_CodigoMadre & "', {" & Date.Now.ToString("MM/dd/yy") & "}, '" & DateTime.Now().ToString("HH:mm:ss") & "', " & _
                " '" & cliente.CLI_Descripcion & "', '" & cliente.CLI_Descripcion2 & "', '" & direccionFiscal & "', '" & formaPago & "', " & _
                " '" & entrega & "', '" & distrito.DIS_Descripcion & "', {" & fechaPedido & "}, '" & cliente.CLI_Marca & "'," & _
                " '" & codAge & "', '" & obs1 & "', '" & obs2 & "', {}, '" & cliente.CLI_LimPro & "', '" & agencia.AGE_Direccion & "', '', '', '0', '', " & _
                " '99', '" & cliente.CLI_Catcli & "', '1', '', 99, {" & Date.Now.ToString("MM/dd/yy") & "},'" & DateTime.Now().ToString("HH:mm:ss") & "', 'C', '')"
                sqlComm = New OdbcCommand(sql, dbConn)
                Resultado = sqlComm.ExecuteNonQuery()

                dbConn.Close()

            Catch ex As Exception
                Return False
            End Try
        End Using
        actualizarCorrDetalle()
        Return True

    End Function

    'Private Function regisCtrDet02(ByVal nroPedido As String, ByVal nroDetalle As String) As Boolean
    '    Dim periodo As String = obtenerPeriodo()
    '    Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRD01.DBF"
    '    sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

    '    Dim sSelect As String = "SELECT ctr.DOCTO, ctr.Ubica, ctr.ITEM, ctr.CODALM, ctr.QENV, ctr.UM, ctr.DESCRIP, " & _
    '        " SUBSTR( SUBSTR(rtrim(art.ubica)+rtrim(art.ubicamas),len(rtrim(art.ubica)+rtrim(art.ubicamas))-5,6),1,2) as rack, " & _
    '        " SUBSTR( SUBSTR(rtrim(art.ubica)+rtrim(art.ubicamas),len(rtrim(art.ubica)+rtrim(art.ubicamas))-5,6),4,2) as fila, " & _
    '        " SUBSTR( SUBSTR(rtrim(art.ubica)+rtrim(art.ubicamas),len(rtrim(art.ubica)+rtrim(art.ubicamas))-5,6),3,1) as lado, " & _
    '        " SUBSTR( SUBSTR(rtrim(art.ubica)+rtrim(art.ubicamas),len(rtrim(art.ubica)+rtrim(art.ubicamas))-5,6),6,1) as columna , " & _
    '        " iif( rck.price > val(SUBSTR( SUBSTR(rtrim(art.ubica)+rtrim(art.ubicamas),len(rtrim(art.ubica)+rtrim(art.ubicamas))-5,6),4,2)) , 1 , 2 ) as piso, " & _
    '        " SUBSTR(rtrim(art.ubica)+rtrim(art.ubicamas),len(rtrim(art.ubica)+rtrim(art.ubicamas))-5,6) as nvoUbica, " & _
    '        " iif( len(rtrim(art.ubicamas))>0 , rtrim(art.ubica) + ' ' + SUBSTR(rtrim(art.ubicamas),1,LEN(rtrim(art.ubicamas))-6) , '' ) as nvoUbicamas " & _
    '        " FROM CTRD01 ctr " & _
    '        " INNER JOIN " & periodo.Trim & "/INVMAST art ON art.item = ctr.item " & _
    '        " LEFT JOIN TABLAS/INFOGEN rck ON code+subcode='5001' and rck.item = SUBSTR(SUBSTR(rtrim(art.ubica)+rtrim(art.ubicamas), Len(rtrim(art.ubica)+rtrim(art.ubicamas)) - 5, 6), 1, 2) " & _
    '        " WHERE ctr.DOCTO='" & nroPedido.ToUpper() & "' " & _
    '        " ORDER BY piso desc, rck.abrev, lado, fila, columna"
    '    Dim respuesta As Boolean
    '    Dim articulo As New Articulo
    '    Try
    '        Using connection As New OdbcConnection(sConn)
    '            connection.Open()
    '            Dim command As New OdbcCommand(sSelect, connection)
    '            Dim reader As OdbcDataReader = command.ExecuteReader()
    '            Dim ordenDeta As Double = 1.0
    '            Dim sSelect2 As String
    '            While reader.Read()
    '                If reader.GetDecimal(4) > 0 Then
    '                    articulo = obtenerArticulo(reader.GetString(2).Trim)
    '                    respuesta = registrarCtrDet02(nroPedido.ToUpper(), nroDetalle, ordenDeta, reader.GetString(12).Trim, articulo, _
    '                                        reader.GetString(3).Trim, reader.GetDecimal(4), reader.GetString(5).Trim, reader.GetString(6).Trim, reader.GetString(13).Trim)
    '                    If articulo.ART_Combo = "S" Then
    '                        sSelect2 = "SELECT codArt,cantidad FROM ENSAMBLE WHERE codigo='" & articulo.ART_Codigo & "'"
    '                        Dim command2 As New OdbcCommand(sSelect2, connection)
    '                        Dim reader2 As OdbcDataReader = command2.ExecuteReader()
    '                        Dim ordenDetaC As Double
    '                        Dim articuloC As New Articulo
    '                        ordenDetaC = ordenDeta
    '                        While reader2.Read()
    '                            ordenDetaC = ordenDetaC + 0.1
    '                            articuloC = obtenerArticulo(reader2.GetString(0).Trim)
    '                            respuesta = registrarCtrDet02(nroPedido.ToUpper(), nroDetalle, ordenDetaC, articuloC.ART_Ubicacion, articuloC, "", reader.GetDecimal(4) * reader2.GetDecimal(1), articuloC.UND_Codigo, articuloC.ART_Descripcion, articuloC.ART_UbicaMas)
    '                        End While
    '                    End If
    '                    ordenDeta = ordenDeta + 1
    '                End If
    '            End While
    '            reader.Close()
    '        End Using
    '    Catch ex As Exception
    '        Return False
    '    End Try

    '    Return respuesta
    'End Function

    Private Function registrarCtrDet02(ByVal nroPedido As String, ByVal nroDetalle As String, ByVal numero As Double, ByVal ubica As String, _
    ByVal articulo As Articulo, ByVal codAlm As String, ByVal cantidad As String, ByVal um As String, ByVal Descrip As String, ByVal ubicaMas As String) As Boolean

        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRDET02.DBF"
        Dim sqlComm As OdbcCommand
        Dim Resultado As Integer

        Dim sConn As String
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sql As String = ""

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()

                sql = "INSERT INTO CTRDET02(NROPED,NRODET,NUMERO,UBICA,CODIGO,CODALM,CANTID,UMEDID,DESCRI,UBICAMAS,LARGO,CODSUC,CODUSU,FCHPRO, HORPRO,ESTPRO,PASPRO) " & _
                "VALUES( " & _
                " '" & nroPedido & "', '" & nroDetalle & "', " & numero & ", '" & ubica & "', '" & articulo.ART_Codigo & "', '" & codAlm & "', " & cantidad & ", " & _
                " '" & um & "', '" & Descrip & "', '" & ubicaMas & "', '" & articulo.ART_Largo & "','', 99, {" & Date.Now.ToString("MM/dd/yy") & "}, '" & DateTime.Now().ToString("HH:mm:ss") & "', 'C', '')"
                sqlComm = New OdbcCommand(sql, dbConn)
                Resultado = sqlComm.ExecuteNonQuery()

                dbConn.Close()

            Catch ex As Exception
                Return ex.Message
            End Try
        End Using

        Return True

    End Function

    Private Function registrarDeta(ByVal nroPedido As String, ByVal nroDetalle As String, ByVal fecha As String, ByVal hora As String, ByVal agencia As String, _
        ByVal cliente As Cliente) As Boolean

        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRDETA.DBF"
        Dim sqlComm As OdbcCommand
        Dim Resultado As Integer

        Dim sConn As String
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sql As String = ""


        Dim codDist As String = cliente.DIS_Codigo

        Dim agenc As New Agencia
        agenc = obtenerAgenciaTransporteDBF(agencia)
        ' CLI_Limpro = 2 Es provincia
        If cliente.CLI_LimPro = "2" Then
            codDist = agenc.DIS_Codigo
        End If


        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                sql = "INSERT INTO CTRDETA(FECHA,DETALLE,CLIENTE,CODMAT,NOMBRE,PEDIDO,HORA,AGENCIA,COPIA,CHOFER,CAMION,FECHADP, " & _
                "HORSAL,HORENT,CANCELA,APROBO,STATUS,HSALIDA,VEHICULO, CODDIST, DESPACHO, HENTRADA, LIMPRO, ESTCLI, FRETORNO, " & _
                "HRETORNO, VRETORNO, PROGRAMA, FECHAPRG, RUTA, TVENTA, TDOC, NUMERO, GUIA ,CODSUC,CODUSU,FCHPRO, HORPRO, ESTPRO,PASPRO)" & _
                " VALUES(" & _
                " {" & fecha & "}, '" & nroDetalle & "', '" & cliente.CLI_Codigo & "', '" & cliente.CLI_CodigoMadre & "', '" & cliente.CLI_Descripcion & "', " & _
                " '" & nroPedido.ToUpper() & "', '" & hora & "', '" & agencia & "', " & _
                " 0, '', '', {}, '', '', '', '', '', '', '', '" & codDist & "','','','" & cliente.CLI_LimPro & "','',{},'','', " & _
                " '',{},'','','','','', " & _
                " '', 99, {" & Date.Now.ToString("MM/dd/yy") & "}, '" & DateTime.Now().ToString("HH:mm:ss") & "', 'C', '')"
                sqlComm = New OdbcCommand(sql, dbConn)
                Resultado = sqlComm.ExecuteNonQuery()

                dbConn.Close()

            Catch ex As Exception
                Return False
            End Try
        End Using

        Return True

    End Function

    Private Function actualizarDsctoCond(ByVal nroPedido As String, ByVal desc As Double) As Boolean
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRD01.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"

        Dim sqlComm As OdbcCommand
        Dim Resultado As Integer
        Using connection As New OdbcConnection(sConn)
            Try
                connection.Open()
                sqlComm = New OdbcCommand("UPDATE CTRD01 SET desc1=" & desc & " WHERE docto='" & nroPedido & "'", connection)
                Resultado = sqlComm.ExecuteNonQuery()
            Catch ex As Exception
                Return False
            End Try
        End Using

        Return acumularDocumentoCliente(nroPedido)
    End Function

    'Function actualizarDocumentoClienteDetalle(ByVal nroPedido As String, ByVal solicitado As Double, ByVal atendido As Double, ByVal articuloId As String, _
    '    ByVal descCondicion As Double, ByVal descuento As Double, ByVal regalo As String, ByVal precRegalo As String, ByVal vendedor As String, _
    '    ByVal tasaPercepcion As Double, ByVal atendidoAnt As Double) As Boolean Implements IService1.actualizarDocumentoClienteDetalle
    '    Dim arti As Articulo
    '    arti = obtenerArticulo(articuloId)
    '    Dim pedi As DocumentoCliente
    '    pedi = obtenerDocumentoCliente(nroPedido.ToUpper)
    '    Dim mensaje As String = ""
    '    If pedi.DCI_Estado.ToUpper = "I" Then
    '        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRD01.DBF"
    '        Dim sqlComm As OdbcCommand
    '        Dim Resultado As Integer

    '        Dim periodo As String = obtenerPeriodo()
    '        Dim mesPeriodo As Integer = Integer.Parse(periodo.Substring(2, 2))

    '        Dim sConn As String
    '        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
    '        Dim sql As String = ""

    '        Dim excedeDscto As String = "0"
    '        If descuento > obtenerDsto(articuloId, atendido) Then
    '            excedeDscto = "1"
    '        End If

    '        Dim pendiente As Double = (solicitado - atendido)
    '        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
    '            Try
    '                dbConn.Open()

    '                sql = "UPDATE CTRD01 SET QORD = " & solicitado & ", QENV = " & atendido & ", QPEN = " & pendiente & ", " & _
    '                " ITEM='" & arti.ART_Codigo & "', DESCRIP = '" & arti.ART_Descripcion & "', UM = '" & arti.UND_Codigo & "', CLASE='" & arti.CLA_Codigo & "'," & _
    '                " COSTO=" & arti.ART_Costos & ", COSTOD = " & arti.ART_CostoD & ", LISTA = " & obtenerNvoPrecio(arti, pedi.DCI_LPrecio, pedi.MON_Codigo) & ", DESC1 = " & descCondicion & ", " & _
    '                " DESC2=" & descuento & ", PERIODO = " & mesPeriodo & ", NCTA = '" & precRegalo & "', ENV='" & regalo & "', ITEM1 = '" & arti.PROV_Codigo & "'," & _
    '                " UBICA='" & arti.ART_Ubicacion & "', LARGO='" & arti.ART_Largo & "', " & _
    '                " TASAPER = " & tasaPercepcion & " WHERE DOCTO='" & nroPedido & "' and ITEM='" & arti.ART_Codigo & "'"

    '                sqlComm = New OdbcCommand(sql, dbConn)
    '                Resultado = sqlComm.ExecuteNonQuery()

    '                If atendido <> atendidoAnt Then
    '                    sql = "UPDATE " & obtenerPeriodo() & "/INVMAST SET compr=compr+" & atendido - atendidoAnt & " WHERE ITEM = '" & arti.ART_Codigo & "'"
    '                    sqlComm = New OdbcCommand(sql, dbConn)
    '                    Resultado = sqlComm.ExecuteNonQuery()
    '                End If

    '                dbConn.Close()
    '                acumularDocumentoCliente(nroPedido)
    '                mensaje = " OK"
    '            Catch ex As Exception
    '                mensaje = sql + " - " + ex.Message
    '                Return False
    '            End Try
    '        End Using
    '        Return True

    '    Else
    '        mensaje = " Estado Pedido Distinto a  I"
    '        Return False
    '    End If
    '    'Return mensaje
    'End Function

    'Private Function actualizaPedidoDxMoneda(ByVal nroPedido As String, ByVal moneda As String, ByVal listaPrecio As String) As Boolean
    '    Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRD01.DBF"
    '    sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
    '    Dim sSelect As String = "SELECT DOCTO, QORD, QENV, ITEM, DESC1, DESC2, ENV, NCTA, TASAPER " & _
    '    "FROM CTRD01 " & _
    '    "WHERE DOCTO='" & nroPedido.ToUpper() & "'"

    '    Dim respuesta As Boolean = True

    '    Using connection As New OdbcConnection(sConn)
    '        connection.Open()
    '        Dim command As New OdbcCommand(sSelect, connection)
    '        Dim reader As OdbcDataReader = command.ExecuteReader()
    '        While reader.Read() And respuesta
    '            'precio = obtenerNvoPrecio(reader.GetString(3).Trim, listaPrecio, moneda)
    '            respuesta = actualizarDocumentoClienteDetalle(reader.GetString(0).Trim, reader.GetDecimal(1), reader.GetDecimal(2), reader.GetString(3).Trim, reader.GetDecimal(4), reader.GetString(5), _
    '            reader.GetString(6).Trim, reader.GetString(7).Trim, "VENDEDOR", reader.GetDecimal(8), reader.GetDecimal(2))
    '        End While
    '        reader.Close()
    '    End Using

    '    Return respuesta
    'End Function

    Private Function actualizaCtrrep01(ByVal nroPedido As String, ByVal status As String, ByVal monto As Double, ByVal formaPago As String) As Boolean
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRREP01.DBF"
        Dim sConn As String
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sqlComm As OdbcCommand
        Dim Resultado As Integer
        Dim sql As String
        Dim sWhere As String = ""
        Dim fPago As Integer = formaPago
        If status.Equals("D") Then
            sWhere = ", FECHAA={" & Date.Now.ToString("MM/dd/yy") & "}, HORAA='" & DateTime.Now().ToString("HH:mm:ss") & "'"
        End If

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                sql = "UPDATE CTRREP01 SET FECHAC={" & Date.Now.ToString("MM/dd/yy") & "}, HORAC='" & DateTime.Now().ToString("HH:mm:ss") & "', " & _
                " MONTO=" & monto & ", LETRA=" & fPago & "" & sWhere & _
                " WHERE PEDIDO = '" & nroPedido.ToUpper() & "'"
                sqlComm = New OdbcCommand(sql, dbConn)
                Resultado = sqlComm.ExecuteNonQuery()
                dbConn.Close()

            Catch ex As Exception
                Return False
            End Try

        End Using

        Return True
    End Function

    Private Function actualizaDocumentoCliente(ByVal nroPedido As String, ByVal nroDetalle As String, ByVal status As String, ByVal direccionEntrega As String, _
         ByVal condicion As String, ByVal formaPago As String, ByVal agen As Agencia, _
         ByVal obs1 As String, ByVal obs2 As String, ByVal obsCredito As String, ByVal cancela As String, ByVal aprAuto As String, ByVal moneda As String, ByVal listaPrecio As String) As Boolean

        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRH01.DBF"
        Dim sConn As String
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sqlComm As OdbcCommand
        Dim Resultado As Integer
        Dim sql As String = ""
        Dim mensaje As String = ""
        Dim respuesta As Boolean

        Dim condic As New FormaPago
        condic = obtenerFormaPago(formaPago)

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                sql = "UPDATE CTRH01 SET status='" & status & "', NCHEQUE='" & nroDetalle & "', " & _
                " CVENTA = '" & condicion & "', TVENTA = '" & formaPago & "', ATRANSX = '" & agen.AGE_Codigo & "', " & _
                " DATRANSX = '" & Replace(agen.AGE_Descripcion, "'", " ") & "', Transpt = '" & Date.Now.ToString("dd/MM") & " " & DateTime.Now().ToString("HH:mm:ss") & "'," & _
                " OBS1 = '" & obs1 & "', OBS2 = '" & obs2 & "', CANCELA = '" & cancela & "', APROBO = '" & aprAuto & "', DETALLE = '" & obsCredito & "', " & _
                " MONEDA = '" & moneda & "', MND = '" & moneda & "', LIPREC = '" & listaPrecio & "', DESCUE= " & condic.FOP_Descuentos & _
                " WHERE DOCTO = '" & nroPedido.ToUpper() & "'"
                sqlComm = New OdbcCommand(sql, dbConn)
                Resultado = sqlComm.ExecuteNonQuery()
                mensaje = "Paso correctamente"
                acumularDocumentoCliente(nroPedido)
                mensaje = mensaje & " " & sql
                respuesta = True
            Catch ex As Exception
                mensaje = "" & ex.Message
                respuesta = False
            End Try
        End Using

        Return respuesta
    End Function

    'Function actualizarDocumentoCliente(ByVal nroPedido As String, ByVal fecha As String, ByVal hora As String, ByVal codCliente As String, _
    'ByVal status As String, ByVal direccionEntrega As String, ByVal totalBruto As Double, ByVal totalNeto As Double, ByVal total As Double, _
    'ByVal dsctoCondicion As Double, ByVal dsctoArticulo As Double, ByVal condicion As String, ByVal formaPago As String, ByVal codTrans As String, _
    'ByVal observacion As String, ByVal obsCredito As String, ByVal totalAtendido As Double, ByVal cancela As String, ByVal mnd As String, _
    'ByVal incPromo As String, ByVal dsctoExcedido As String, ByVal totCobrar As Double, ByVal moneda As String, ByVal listaPrecio As String) As Boolean Implements IService1.actualizarDocumentoCliente
    '    Dim respuesta As Boolean = False
    '    Dim mensaje As String = ""
    '    Dim pedido As DocumentoCliente

    '    pedido = obtenerDocumentoCliente(nroPedido.ToUpper)

    '    If pedido.DCI_Estado.ToUpper = "I" Then

    '        If pedido.MON_Codigo <> moneda Then
    '            actualizaPedidoDxMoneda(pedido.DCI_Codigo, moneda, listaPrecio)
    '        End If

    '        Dim cliente As New Cliente
    '        cliente = obtenerCliente(codCliente)

    '        Dim condic As New FormaPago
    '        condic = obtenerFormaPago(formaPago)

    '        If Not actualizarDsctoCond(pedido.DCI_Codigo, condic.FOP_Descuentos) Then
    '            Return False
    '        End If

    '        Dim estado = status
    '        Dim aproAuto = ""
    '        Dim nroDetalle As String = ""
    '        Dim observ1 As String = ""
    '        Dim observ2 As String = ""
    '        If observacion.Length > 0 Then
    '            observ1 = observacion.Substring(0, observacion.Length)
    '            If observacion.Length > 80 Then
    '                observ1 = observacion.Substring(0, 80)
    '                observ2 = observacion.Substring(80, observacion.Length - 80)
    '                If observacion.Length > 160 Then
    '                    observ2 = observacion.Substring(80, 160)
    '                End If

    '            End If
    '        End If
    '        Dim ctrdet01, ctrdet02, ctrdeta, ctraprob As Boolean
    '        Dim ctrRep01 As Boolean = False
    '        Dim ctrPedido As Boolean = False
    '        Dim aprobAuto As Boolean = False
    '        mensaje = mensaje & "Cliente Bloqueado: " & cliente.CLI_Bloqueado & " Obliga Creditos : " & condic.FOP_ObligaCreditos

    '        If condicion.Equals("C") Or incPromo.Equals("1") Or dsctoExcedido.Equals("1") Or (formaPago = "88" And cliente.CLI_DParcial.Equals("N")) Then
    '            estado = "I"
    '            If formaPago = "88" And cliente.CLI_DParcial.Equals("N") Then
    '                obsCredito = obsCredito & " DESPACHO PARCIAL NO PERMITIDO"
    '            End If
    '        Else
    '            If status.Equals("C") And cliente.CLI_Bloqueado.Equals("0") And condic.FOP_ObligaCreditos.Equals("0") Then
    '                If (cliente.CLI_SaldoConSobregiro > total) Or condicion.Equals("C") Then

    '                    mensaje = mensaje & " Aprobo automatico"
    '                    aprobAuto = True
    '                    nroDetalle = obtenerCorrDetalle()
    '                    ctrdet01 = registrarCtrDet01(nroPedido.ToUpper, obtenerCorrDetalle().ToString, cliente, formaPago, fecha, codTrans, observ1, observ2)
    '                    ctrdet02 = regisCtrDet02(nroPedido.ToUpper, nroDetalle)
    '                    ctrdeta = registrarDeta(nroPedido.ToUpper, nroDetalle, Date.Now.ToString("MM/dd/yy"), DateTime.Now().ToString("HH:mm:ss"), codTrans, cliente)
    '                    ctraprob = registrarAprob(nroPedido.ToUpper, nroDetalle, Date.Now.ToString("MM/dd/yy"), DateTime.Now().ToString("HH:mm:ss"), condicion, formaPago, nroPedido.Substring(0, 2).ToUpper(), moneda, totalBruto, total, cliente, "S", totCobrar)
    '                    If ctrdet01 And ctrdet02 And ctrdeta And ctraprob Then
    '                        estado = "D"
    '                        aproAuto = "S"
    '                        mensaje = mensaje & "Paso" & ctrdet01 & "-" & ctrdet02 & "-" & ctrdeta & "-" & ctraprob
    '                    Else
    '                        If Not ctrdet01 Then
    '                            mensaje = mensaje & "Fallo ctrDet01"
    '                        End If
    '                        If Not ctrdet02 Then
    '                            mensaje = mensaje & "Fallo ctrdet02"
    '                        End If
    '                        If Not ctrdeta Then
    '                            mensaje = mensaje & "Fallo ctrdeta"
    '                        End If
    '                        If Not ctraprob Then
    '                            mensaje = mensaje & "Fallo ctraprob"
    '                        End If
    '                        eliminarAprobacion(nroPedido)
    '                    End If
    '                Else
    '                    mensaje = mensaje & " Client sobregirado Monto: " & cliente.CLI_SaldoConSobregiro & " Pedido : " & total
    '                End If
    '            End If
    '        End If

    '        'Dim borrar As String = ""

    '        If (estado = "D" And aprobAuto = True) Or (estado.Equals("C") And aprobAuto = False) Or (estado = "I" And aprobAuto = False) Then
    '            ctrRep01 = actualizaCtrrep01(nroPedido.ToUpper, estado, total, formaPago)
    '            ctrPedido = actualizaDocumentoCliente(nroPedido.ToUpper, nroDetalle, estado, direccionEntrega, condicion, formaPago, obtenerAgenciaTransporteDBF(codTrans), observ1, observ2, obsCredito, cancela, aproAuto, moneda, listaPrecio)
    '            'mensaje = mensaje + " " + actualizaPedido(nroPedido.ToUpper, nroDetalle, estado, direccionEntrega, condicion, formaPago, obtenerAgencia(codTrans), observ1, observ2, obsCredito, cancela, aproAuto, moneda, listaPrecio)
    '            'borrar = actualizaPedido(nroPedido.ToUpper, nroDetalle, estado, direccionEntrega, condicion, formaPago, obtenerAgencia(codTrans), observ1, observ2, obsCredito, cancela, aproAuto, moneda, listaPrecio)
    '            mensaje = mensaje & " Paso " & ctrRep01 & "  " & ctrPedido
    '        Else
    '            mensaje = mensaje & "Condicion " & condicion & " Estado : " & estado & " AproAuto : " & aprobAuto & " condicion : " & condicion & " incPromo : " & incPromo & " DsctoExcedido : " & dsctoExcedido
    '            respuesta = False
    '        End If

    '        If ctrRep01 And ctrPedido Then
    '            respuesta = True
    '            confirmarImpresion(nroDetalle)
    '            mensaje = mensaje & ctrRep01 & " - " & ctrPedido
    '        Else
    '            If Not ctrRep01 Then
    '                mensaje = mensaje & " Fallo ctrRep01"
    '            End If
    '            If Not ctrPedido Then
    '                mensaje = mensaje & " Fallo ctrPedido " '& borrar
    '            End If
    '            respuesta = False
    '            eliminarAprobacion(nroPedido.ToUpper)
    '        End If
    '    Else
    '        mensaje = "Pedido has sido modificado por otro Sistema. Confirme con su sectorista"
    '    End If
    '    'Return mensaje
    '    Return respuesta
    'End Function

    Private Function anularDocumentoClienteDetalle(ByVal nroPedido As String) As Boolean
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRD01.DBF"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sSelect As String = "SELECT DOCTO, ITEM, QENV " & _
        "FROM CTRD01 " & _
        "WHERE DOCTO='" & nroPedido.ToUpper() & "' "

        Dim respuesta As Boolean = True

        Using connection As New OdbcConnection(sConn)
            connection.Open()
            Dim command As New OdbcCommand(sSelect, connection)
            Dim reader As OdbcDataReader = command.ExecuteReader()
            While reader.Read() And respuesta
                respuesta = eliminarDocumentoClienteDetalle(reader.GetString(0).Trim, reader.GetString(1).Trim, reader.GetDecimal(2))
            End While
            reader.Close()
        End Using

        Return respuesta
    End Function

    Function anularDocumentoCliente(ByVal nroPedido As String) As Boolean Implements IService1.anularDocumentoCliente
        If obtenerDocumentoCliente(nroPedido.ToUpper).DCI_Estado.ToUpper = "I" Then
            Dim anulPedidoDetall As Boolean = anularDocumentoClienteDetalle(nroPedido)

            If Not anulPedidoDetall Then
                Return False
            End If
            Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\CTRH01.DBF"
            Dim sConn As String
            sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
            Dim sqlComm As OdbcCommand
            Dim Resultado As Integer
            Dim sql As String

            Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
                Try
                    dbConn.Open()

                    sql = "UPDATE CTRREP01 SET observ='ANULADO' WHERE PEDIDO = '" & nroPedido.ToUpper() & "'"
                    sqlComm = New OdbcCommand(sql, dbConn)
                    Resultado = sqlComm.ExecuteNonQuery()

                    sql = "DELETE FROM CTRH01 WHERE DOCTO='" & nroPedido.ToUpper() & "'"
                    sqlComm = New OdbcCommand(sql, dbConn)
                    Resultado = sqlComm.ExecuteNonQuery()

                    dbConn.Close()
                Catch ex As Exception
                    Return False
                End Try

            End Using
            Return True
        Else
            Return False
        End If
    End Function

    Function registrarDocumentoClienteSQL(dc As DocumentoCliente) As Boolean Implements IService1.registrarDocumentoClienteSQL
        If conectarSQL() = True Then
            Dim comando As SqlCommand = cnn.CreateCommand
            Dim transaction As SqlTransaction = Nothing

            Try

                Dim query As String = "INSERT INTO Documento_Cliente(CIA,SUC_Codigo,TDOC_Codigo,DCI_Codigo,DCI_Fecha,CLI_Codigo, " &
                                      "VEN_Codigo,MON_Codigo,TDO_Codigo,CNV_Codigo,FOP_Codigo,AGE_Codigo,DCI_TotalBruto,DCI_TotalDsct, " &
                                      "DCI_TotalNeto,DCI_IGV,DCI_Total,DCI_UltModificacion,DCI_Observaciones,DCI_ObsCredito, " &
                                      "DCI_DireccionEntrega,CLEN_Codigo,DCI_ExcedeDscto,DCI_TotalDsctoCond,DCI_TotalSoles,DCI_TotalDolares, " &
                                      "DCI_Percepcion,DCI_TotalCobrar,DCI_Balanza,RUT_Codigo,DCI_Estado,DCI_Programado,DCI_Rebote) " &
                                      "VALUES(@CIA,@SUC,@TDOC,@DCI,@FEC,@CLI,@VEN,@MON,@TDO,@CNV,@FOP,@AGE,@BRU,@TDSC,@TNET,@IGV,@TOT,@ULMOD, " &
                                      "@OBS,@OBSC,@DIREN,@CLEN,@EXCDSC,@TDSCCN,@TOTSOL,@TOTDOL,@PERC,@TOTCO,@BAL,@RUT,@EST,@PROG,@REB)"
                With comando
                    .CommandText = query
                    .Parameters.AddWithValue("@CIA", dc.CIA)
                    .Parameters.AddWithValue("@SUC", dc.SUC_Codigo)
                    .Parameters.AddWithValue("@TDOC", dc.TDOC_Codigo)
                    .Parameters.AddWithValue("@DCI", dc.DCI_Codigo)
                    .Parameters.AddWithValue("@FEC", dc.DCI_Fecha)
                    .Parameters.AddWithValue("@CLI", dc.CLI_Codigo)
                    .Parameters.AddWithValue("@VEN", dc.VEN_Codigo)
                    .Parameters.AddWithValue("@MON", dc.MON_Codigo)
                    .Parameters.AddWithValue("@TDO", dc.TDO_Codigo)
                    .Parameters.AddWithValue("@CNV", dc.CNV_Codigo)
                    .Parameters.AddWithValue("@FOP", dc.FOP_Codigo)
                    .Parameters.AddWithValue("@AGE", dc.AGE_Codigo)
                    .Parameters.AddWithValue("@BRU", dc.DCI_TotalBruto)
                    .Parameters.AddWithValue("@TDSC", dc.DCI_TotalDsct)
                    .Parameters.AddWithValue("@TNET", dc.DCI_TotalNeto)
                    .Parameters.AddWithValue("@IGV", dc.DCI_IGV)
                    .Parameters.AddWithValue("@TOT", dc.DCI_Total)
                    .Parameters.AddWithValue("@ULMOD", dc.DCI_UltModificacion)
                    .Parameters.AddWithValue("@OBS", dc.DCI_Observaciones)
                    .Parameters.AddWithValue("@OBSC", dc.DCI_ObsCredito)
                    .Parameters.AddWithValue("@DIREN", dc.DCI_DireccionEntrega)
                    .Parameters.AddWithValue("@CLEN", dc.CLEN_Codigo)
                    .Parameters.AddWithValue("@EXCDSC", dc.DCI_ExcedeDscto)
                    .Parameters.AddWithValue("@TDSCCN", dc.DCI_TotalDsctoCond)
                    .Parameters.AddWithValue("@TOTSOL", dc.DCI_TotalSoles)
                    .Parameters.AddWithValue("@TOTDOL", dc.DCI_TotalDolares)
                    .Parameters.AddWithValue("@PERC", dc.DCI_Percepcion)
                    .Parameters.AddWithValue("@TOTCO", dc.DCI_TotalCobrar)
                    .Parameters.AddWithValue("@BAL", dc.DCI_Balanza)
                    .Parameters.AddWithValue("@RUT", dc.RUT_Codigo)
                    .Parameters.AddWithValue("@EST", dc.DCI_Estado)
                    .Parameters.AddWithValue("@PROG", dc.DCI_Programado)
                    .Parameters.AddWithValue("@REB", dc.DCI_Rebote)
                    .ExecuteNonQuery()
                End With
                transaction.Commit()
                Return True
            Catch ex As Exception
                transaction.Rollback()
                Return False
            Finally
                desconectarSQL()
            End Try
        Else
            Return False
        End If
    End Function

    'Function registrarDocumentoClienteDetalleSQL(dcd As DocumentoClienteDetalle) As Boolean Implements IService1.registrarDocumentoClienteDetalleSQL
    '    If conectarSQL() = True Then
    '        Dim comando As SqlCommand = cnn.CreateCommand
    '        Dim transaction As SqlTransaction = Nothing

    '        Try

    '            Dim query As String = "INSERT INTO Documento_Cliente_Detalle(CIA,SUC,TDOC_Codigo,DCI_Codigo,DCD_Codigo, " &
    '                                  "DCD_Fecha,ART_Codigo,ALM_Codigo,DCD_Ordenado,DCD_Atendido,DCD_Descuento,DCD_ValorUnit, " &
    '                                  "DCD_UltModificacion, DCD_DCDCodigo, DCD_DsctoAdicional, DCD_ValorUnitSoles, DCD_ValorUnitDolares, " &
    '                                  "DCD_DsctoCond,DCD_PorcPerc,DCD_FechasLlegada,DCD_Activo) " &
    '                                  "VALUES(@CIA,@SUC,@TDOC,@DCI,@DCD,@FEC,@ART,@ALM,@ORD,@ATE,@DES,@VUNI, " &
    '                                  "@UMOD,@DCDDCD,@DSAD,@VUS,@VUD,@DSCON,@POPER,@FECLLE,@ACT)"
    '            With comando
    '                .CommandText = query
    '                .Parameters.AddWithValue("@CIA", dcd.CIA)
    '                .Parameters.AddWithValue("@SUC", dcd.SUC_Codigo)
    '                .Parameters.AddWithValue("@TDOC", dcd.TDOC_Codigo)
    '                .Parameters.AddWithValue("@DCI", dcd.DCI_Codigo)
    '                .Parameters.AddWithValue("@DCD", dcd.DCD_Codigo)
    '                .Parameters.AddWithValue("@FEC", dcd.DCD_Fecha)
    '                .Parameters.AddWithValue("@ART", dcd.ART_Codigo)
    '                .Parameters.AddWithValue("@ALM", dcd.ALM_Codigo)
    '                .Parameters.AddWithValue("@ORD", dcd.DCD_Ordenado)
    '                .Parameters.AddWithValue("@ATE", dcd.DCD_Atendido)
    '                .Parameters.AddWithValue("@DES", dcd.DCD_Descuento)
    '                .Parameters.AddWithValue("@VUNI", dcd.DCD_ValorUnit)
    '                .Parameters.AddWithValue("@UMOD", dcd.DCD_UltModificacion)
    '                .Parameters.AddWithValue("@DCDDCD", dcd.DCD_DCDCodigo)
    '                .Parameters.AddWithValue("@DSAD", dcd.DCD_DsctoAdicional)
    '                .Parameters.AddWithValue("@VUS", dcd.DCD_ValorUnitSoles)
    '                .Parameters.AddWithValue("@VUD", dcd.DCD_ValorUnitDolares)
    '                .Parameters.AddWithValue("@DSCON", dcd.DCD_DsctoCond)
    '                .Parameters.AddWithValue("@POPER", dcd.DCD_PorcPerc)
    '                .Parameters.AddWithValue("@FECLLE", dcd.DCD_FechasLlegada)
    '                .Parameters.AddWithValue("@ACT", dcd.DCD_Activo)
    '                .ExecuteNonQuery()
    '            End With
    '            transaction.Commit()
    '            Return True
    '        Catch ex As Exception
    '            transaction.Rollback()
    '            Return False
    '        Finally
    '            desconectarSQL()
    '        End Try
    '    Else
    '        Return False
    '    End If
    'End Function

    Function DocumentosDeuda(ByVal cliente As String) As DocumentoCliente() Implements IService1.DocumentosDeuda
        Dim sBase As String = "\\\\GULDABD01\\Datos Aplicacion\\DATOS\\GULDA\\INFOGEN.DBF"
        Dim periodo As String = obtenerPeriodo()
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(sBase) & ";"
        Dim sSelect As String = "SELECT td.abrev as 'TIPO', refe as 'NUMERO', fecha4 as 'F. EMISION', fecha2 as 'F. VENCIMIENTO', iif(arp.mnd='1','US$/.','  S/.') as 'Moneda', " & _
        " max(monto1) as 'MONTO', sum(iif(treg=1,1,-1) * monto1) as 'SALDO', " & _
        " iif(arp.doc='5' ,esl.descrip2 ,'              ' ) as 'ESTADO', iif(arp.doc='5' , ban.abrev,'                      ' )as 'BANCO', " & _
        " iif(esl.descrip2='EN BANCO' ,nlbanco,'              ') as 'NRO UNICO' " & _
        "FROM " & periodo.Trim & "/arpendsa arp " & _
        "INNER JOIN tablas/infogen td ON td.code+td.subcode='TD01' and td.item=arp.doc " & _
        "INNER JOIN tablas/infogen ban ON ban.code+ban.subcode='0801' and ban.item = arp.bco " & _
        "LEFT JOIN tablas/infogen esl ON esl.code+esl.subcode='LT01' and esl.item = arp.estado " & _
        "WHERE vendor='" & cliente & "' " & _
        "GROUP BY td.descrip, refe " & _
        "ORDER BY fecha2"

        Using connection As New OdbcConnection(sConn)

            connection.Open()
            Dim command As New OdbcCommand(sSelect, connection)

            Dim reader As OdbcDataReader = command.ExecuteReader()
            'ARRAY LIST
            Dim lista As New ArrayList()

            While reader.Read()
                Dim deuda As New DocumentoCliente(reader.GetString(0).Trim, reader.GetString(1).Trim, reader.GetString(2).Trim, reader.GetString(3).Trim, _
                                                  reader.GetString(4).Trim, reader.GetDouble(5), reader.GetDouble(6), reader.GetString(7), reader.GetString(8), reader.GetString(9))
                lista.Add(deuda)
            End While

            Dim i As Integer = 0

            Dim deudas(lista.Count - 1) As DocumentoCliente
            While lista.Count > i
                deudas(i) = lista(i)
                i = i + 1
            End While

            ' Call Close when done reading.
            reader.Close()

            Return deudas

        End Using
    End Function

End Class
